JSON and XML in SQL Server
This post is the first in a series of blog posts around using XML and JSON in SQL Server. XML has been supported, although slightly clunky since SQL Server 2005, and it has not changed much. Later versions of SQL Server introduce some JSON parsing which is different from how XML is represented in that JSON data is just stored as NVARCHAR data.
Here is an example table with some sample data inserted.
CREATE TABLE XmlAndJson
(
id INTEGER IDENTITY NOT NULL,
XmlData XML,
JsonData NVARCHAR(MAX)
);
GO
ALTER TABLE XmlAndJson
ADD CONSTRAINT [JsonData record should be formatted as JSON]
CHECK (ISJSON(JsonData)=1);
GO
SET NOCOUNT ON;
DECLARE @XmlData as XML;
set @XmlData = '<employees>
<employee>
<firstName>Steve</firstName>
<lastName>Stedman</lastName>
</employee>
<employee>
<firstName>Derrick</firstName>
<lastName>Bovenkamp</lastName>
</employee>
<employee>
<firstName>Bill</firstName>
<lastName>Simpkins</lastName>
</employee>
</employees>';
DECLARE @JsonData as VARCHAR(MAX);
set @JsonData = '
{"employees":[
{ "firstName":"Steve", "lastName":"Stedman" },
{ "firstName":"Derrick", "lastName":"Bovenkamp" },
{ "firstName":"Bill", "lastName":"Simpkins" }
]}';
The XML is stored as a SQL Server datatype called XML, however the JSON is just stored as an NVARCHAR(MAX) column.
Stay tuned for other posts on XML and JSON over the next several days.
Related Links:
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply