{"id":1804,"date":"2014-05-06T00:00:00","date_gmt":"2014-05-06T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/producing-json-documents-from-sql-server-queries-via-tsql\/"},"modified":"2021-09-29T16:21:38","modified_gmt":"2021-09-29T16:21:38","slug":"producing-json-documents-from-sql-server-queries-via-tsql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/producing-json-documents-from-sql-server-queries-via-tsql\/","title":{"rendered":"Producing JSON Documents from SQL Server queries via TSQL"},"content":{"rendered":"
\n

Articles by Phil Factor about JSON and SQL Server:<\/h4>\n
    \n
  1. Consuming JSON Strings in SQL Server (Nov 2012)<\/a> <\/li>\n
  2. SQL Server JSON to Table and Table to JSON (March 2013)<\/a> <\/li>\n
  3. Producing JSON Documents From SQL Server Queries via TSQL (May 2014)<\/a> <\/li>\n
  4. Consuming hierarchical JSON documents in SQL Server using OpenJSON (Sept 2017)<\/a> <\/li>\n
  5. Importing JSON data from Web Services and Applications into SQL Server(October 2017)<\/a> <\/li>\n<\/ol>\n\n

    If you need to provide JSON-based results from the database, you are faced with a problem, because SQL Server doesn’t , at the time of writing this, \u00a0have native JSON-integration (see the article ‘Consuming hierarchical JSON documents in SQL Server using OpenJSON<\/a>‘ in the list above for a SQL Server 2016 solution). You would normally \u00a0craft the SQL to do this ‘by hand’ for the specific job, using one of a variety of techniques. However, what if the developers needed to have large variety of results, maybe even from SQL created by the application rather than the database? That’s cool, though there will be a performance hit to using a generic solution that will produce JSON from any SQL. Until SQL Server provides ‘native’ JSON support it will be rather slow, but not difficult.<\/p>\n

    In this article, I’ll be introducing a few ideas about providing a generic way to produce JSON and other types of \u00a0data documents, from SQL.<\/p>\n

    The simplest strategy to provide a general way to output JSON is to convert your SQL result to XML by using the FOR XML<\/code> syntax of the SELECT<\/code> statement.\u00a0 Then you parse the XML, which has obligingly converted all the values into string form, gives you the names, their position in the hierarchy and even the DataType. When I say ‘simple’ I mean simpler than any other alternative. The advantage of using XML is that you can make use of the versatility of the WITH XML PATH<\/code> syntax, or the XPath SQL Server extensions, to specify the hierarchy. If you have a generic way to convert \u00a0from any XML document, whether derived from SQL or not, to JSON, then it becomes even more useful!<\/p>\n

    If you’re not too concerned with performance, you can experiment with some functions I’ve published over the years for dealing with data documents. For handling the type of hierarchical information that is transferred via JSON, CSV or XML, I’ve published a variety of stored procedures and functions that all use a rather crude hierarchy table that is sufficient for the purpose. There is already a function that produces a JSON document from such a table. All we have to do is to add a new Table-Valued function that produces a hierarchy from XML. We then just create the SQL we want, get it to create XML and the rest is simple.<\/p>\n

    Back to AdventureWorks 2008, and here is an example of its use.<\/p>\n

    DECLARE @MyHierarchy Hierarchy -- to pass the hierarchy table around\r\ninsert into @MyHierarchy SELECT * from dbo.ParseXML(\r\n---your SQL Goes here --->\r\n\u00a0 (SELECT top 5 title,firstname,middleName,Lastname,suffix,emailAddress,Phone, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AddressLine1,AddressLine2,City,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 postalcode,SP.Name,SP.CountryRegionCode\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 as ContactString\r\n\u00a0\u00a0\u00a0 from AdventureWorks.person.contact c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 inner join AdventureWorks.Sales.Individual\u00a0 I ON C.ContactID = I.ContactID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 inner join AdventureWorks.Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 inner join AdventureWorks.Person.Address AS A ON A.AddressID = CA.AddressID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 inner join AdventureWorks.person.StateProvince SP ON A.StateProvinceID=SP.StateProvinceID\r\n---You add this magic spell, making it XML, and giving a name for the 'list' of rows and the root\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0 for XML path ('customer'), root('customers')\r\n-- end of SQL\r\n\u00a0 )\r\n)\r\nSELECT dbo.ToJSON(@MyHierarchy)\r\n<\/pre>\n
    Well, that’s it. You just give the function consisting of any working SQL Query, adding the FOR XML PATH, ROOT \u00a0to turn it into XML \u00a0and you’ll get JSON, reflecting the structures you specify in the FOR XML query. As in much of life, there is some detail to attend to, which we’ll go into later.\u00a0 Firstly, this is fine for the small stuff, but this requires a lot of string manipulation, and SQL isn’t designed for doing that. If you just want to churn out JSON, we’ll also show you a ‘quick’ version that will dish out\u00a0 20,000 rows of ‘flattened’ JSON \u00a0in around ten to fifteen seconds. The limitations are that you lose any attributes, and you have to use the simple default ‘root’ and ‘row’ structure of the XML, but none of that will matter for the common jobs since you don’t need attributes, or nesting, and if you do simple queries with FOR XML PATH, ROOT<\/code> you don’t get ’em.\u00a0 So here we go with an example,<\/p>\n
    SELECT dbo.FlattenedJSON(\r\n\u00a0 (SELECT top 20 o.SalesOrderID, o.OrderDate, od.ProductID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p.Name, od.OrderQty, od.UnitPrice, od.LineTotal\r\n\u00a0\u00a0 FROM AdventureWorks.Sales.SalesOrderHeader AS o\r\n\u00a0\u00a0\u00a0\u00a0 JOIN AdventureWorks.Sales.SalesOrderDetail AS od\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON o.SalesOrderID = od.SalesOrderID\r\n\u00a0\u00a0\u00a0\u00a0 JOIN AdventureWorks.Production.Product AS p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON od.ProductID = p.ProductID\r\n\u00a0\u00a0 WHERE p.Name like 'Road%'\u00a0 \r\n\u00a0\u00a0 FOR XML path, root)\r\n\u00a0 )\r\n<\/pre>\n
    And there you have your JSON ‘document’.<\/p>\n
    JSON representation of tabular results.<\/h1>\n
    Although there is no ANSI SQL way of representing results from SQL Expressions as JSON, SELECT<\/code> statements usually produce results that are represented by ‘flattened’ \u00a0or ‘raw’ JSON lists, like this<\/p>\n
    [\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Red, 44\",\"OrderQty\":\"1\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-450 Red, 52\",\"OrderQty\":\"1\",\"UnitPrice\":\"874.7940\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Red, 52\",\"OrderQty\":\"3\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Black, 52\",\"OrderQty\":\"5\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-450 Red, 58\",\"OrderQty\":\"4\",\"UnitPrice\":\"874.7940\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Red, 44\",\"OrderQty\":\"5\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Black, 58\",\"OrderQty\":\"3\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Black, 44\",\"OrderQty\":\"2\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-150 Red, 56\",\"OrderQty\":\"1\",\"UnitPrice\":\"2146.9620\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-450 Red, 44\",\"OrderQty\":\"1\",\"UnitPrice\":\"874.7940\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Red, 48\",\"OrderQty\":\"3\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-450 Red, 52\",\"OrderQty\":\"6\",\"UnitPrice\":\"874.7940\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-150 Red, 62\",\"OrderQty\":\"1\",\"UnitPrice\":\"2146.9620\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Red, 60\",\"OrderQty\":\"3\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Black, 60\",\"OrderQty\":\"3\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-450 Red, 60\",\"OrderQty\":\"1\",\"UnitPrice\":\"874.7940\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Red, 60\",\"OrderQty\":\"1\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-650 Red, 52\",\"OrderQty\":\"1\",\"UnitPrice\":\"419.4589\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-150 Red, 56\",\"OrderQty\":\"1\",\"UnitPrice\":\"2146.9620\"},\r\n\u00a0\u00a0\u00a0 {\"Name\":\"Road-450 Red, 44\",\"OrderQty\":\"1\",\"UnitPrice\":\"874.7940\"}\r\n]\r\n<\/pre>\n
    I just did the top twenty records just so we could see them in the article, but this should be good \u00a0for 20,000 in a batch.<\/p>\n
    But \u00a0this is not the only way of doing it. We can take the opportunity of JSON to return a nested hierarchy of data<\/p>\n
    \u00a0\u00a0\u00a0{ \"employee\":\r\n\u00a0\u00a0\u00a0\u00a0\u00a0{ \"EmployeeID\" : 1537,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\"Sale_Order\" : [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{ \"CustomerID\" : 6812 , \"Region\" : \"East Anglia\" },\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0...\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{ \"CustomerID\" : 2543 , \"Region\" : \"Wales\" }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0{ \"employee\" :\r\n\u00a0\u00a0\u00a0\u00a0\u00a0{ \"Employee_ID\" : 5723,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\"Sale_Order\" : [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{ \"Customer_ID\" : 234 , \"Region\" : \"London\" },\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0...\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{ \"Customer_ID\" : 68125 , \"Region\" : \"Midlands\" }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  \u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0}\r\n<\/pre>\n
    Or even using a similar device to CSV<\/p>\n
    {\r\n\u00a0 \"columns\":[ \"Make\", \"Model\", \"Year\", \"Color\", \"Mileage\"\r\n\u00a0 ],\r\n\u00a0 \"results\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [\"Volkswagen\",\"Eurovan\",\"2003\",\"White\",\"56,830\"],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [\"Honda\",\"CRV\",\"2009\",\"Black\",\"35,600\"]\r\n\u00a0 ]\r\n}\r\n<\/pre>\n
    Once they are suitably compressed, the obvious space-savings tend to vanish so the formats are down to personal preference. Also, the ‘flattened’ format can be formatted with indents to make it easier to read.\u00a0<\/p>\n
    [\r\n\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"Name\":\"Road-650\u00a0Red,\u00a044\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"OrderQty\":\"1\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"UnitPrice\":\"419.4589\"\r\n\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"Name\":\"Road-650\u00a0Red,\u00a048\",\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\"OrderQty\":\"3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\"UnitPrice\":\"419.4589\"\r\n\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"Name\":\"Road-650\u00a0Red,\u00a052\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"OrderQty\":\"1\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\"UnitPrice\":\"419.4589\"\r\n\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"Name\":\"Road-150\u00a0Red,\u00a056\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"OrderQty\":\"1\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\"UnitPrice\":\"2146.9620\"\r\n\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"Name\":\"Road-450\u00a0Red,\u00a044\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\"OrderQty\":\"1\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"UnitPrice\":\"874.7940\"\r\n\u00a0\u00a0\u00a0}\r\n]\r\n<\/pre>\n
    Getting your JSON out fast.<\/h1>\n
    Because SQL produces, from the ‘document’ perspective, \u00a0flat rows of values, we can cut some corners to get JSON out fast. Firstly, we’ll insist that the root is called ‘root’ and each row is called ‘row’.\u00a0 All we have to worry about now is to render every row in JSON format, with the right separators and brackets to represent a JSON list of\u00a0 objects, which consist of name\/Value pairs.<\/p>\n
    Here is a simple version<\/p>\n
    DECLARE @cars xml;\r\nSET @cars =\r\n'<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<root>\r\n\u00a0 <row>\r\n\u00a0\u00a0\u00a0 <Make>Volkswagen<\/Make>\r\n\u00a0\u00a0\u00a0 <Model>Eurovan<\/Model>\r\n\u00a0\u00a0\u00a0 <Year>2003<\/Year>\r\n\u00a0\u00a0\u00a0 <Color>White<\/Color>\r\n\u00a0 <\/row>\r\n\u00a0 <row>\r\n\u00a0\u00a0\u00a0 <Make>Honda<\/Make>\r\n\u00a0\u00a0\u00a0 <Model>CRV<\/Model>\r\n\u00a0\u00a0\u00a0 <Year>2009<\/Year>\r\n\u00a0\u00a0\u00a0 <Color>Black<\/Color>\r\n\u00a0\u00a0\u00a0 <Mileage>35,600<\/Mileage>\r\n\u00a0 <\/row>\r\n<\/root>';\r\n\r\nSELECT Stuff( --we want to snip out the leading comma\r\n\u00a0 (SELECT TheLine from --this is to glue each row into a string\r\n\u00a0\u00a0\u00a0 (SELECT ',\r\n\u00a0\u00a0\u00a0 {'+ --this is the start of the row, representing the row object in the JSON list\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 --the local-name(.) is an eXPath function that gives you the name of the node\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Stuff((SELECT ',\"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(255)'),'')+'\":\"'+\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 b.c.value('text()[1]','NVARCHAR(MAX)') +'\"' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- 'text()[1]' gives you the text contained in the node\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from x.a.nodes('*') b(c) --get the row XML and split it into each node\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for xml path(''),TYPE).value('(.\/text())[1]','NVARCHAR(MAX)')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,1,1,'')+'}'--remove the first comma \r\n\u00a0\u00a0 from @Cars.nodes('\/root\/*') x(a) --get every row\r\n\u00a0\u00a0 ) JSON(theLine) --each row \r\n\u00a0 for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )\r\n,1,1,'')--remove the first leading comma\r\n<\/pre>\n
    Which would give you this<\/p>\n
    \u00a0\u00a0\u00a0 {\"Make\":\"Volkswagen\",\"Model\":\"Eurovan\",\"Year\":\"2003\",\"Color\":\"White\"},\r\n\u00a0\u00a0\u00a0 {\"Make\":\"Honda\",\"Model\":\"CRV\",\"Year\":\"2009\",\"Color\":\"Black\",\"Mileage\":\"35,600\"}\r\n\r\n<\/pre>\n
    Hmm. This seems OK. I’ve tried several methods but this seems to work best. I don’t profess any expertise in SQL XML querying so please let me know if there is a faster way of doing this!<\/p>\n
    We now wrap it into a function and take care of properly escaping various whitespace characters<\/p>\n
    IF\u00a0OBJECT_ID\u00a0(N'dbo.FlattenedJSON')\u00a0IS\u00a0NOT NULL\r\n\u00a0\u00a0\u00a0DROP FUNCTION\u00a0dbo.FlattenedJSON\r\nGO\r\nCREATE FUNCTION\u00a0dbo.FlattenedJSON\u00a0(@XMLResult\u00a0XML)\r\nRETURNS NVARCHAR(MAX)\r\nWITH EXECUTE AS\u00a0CALLER\r\nAS\r\nBEGIN\r\nDECLARE\u00a0\u00a0@JSONVersion\u00a0NVARCHAR(MAX),\u00a0@Rowcount\u00a0INT\r\nSELECT\u00a0@JSONVersion\u00a0=\u00a0'',\u00a0@rowcount=COUNT(*)\u00a0FROM\u00a0@XMLResult.nodes('\/root\/*')\u00a0x(a)\r\nSELECT\u00a0@JSONVersion=@JSONVersion+\r\nSTUFF(\r\n\u00a0\u00a0(SELECT\u00a0TheLine\u00a0FROM\u00a0\r\n\u00a0\u00a0\u00a0\u00a0(SELECT\u00a0',\r\n\u00a0\u00a0\u00a0\u00a0{'+\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0STUFF((SELECT\u00a0',\"'+COALESCE(b.c.value('local-name(.)',\u00a0'NVARCHAR(255)'),'')+'\":\"'+\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0REPLACE(\u00a0--escape tab properly within a value\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0REPLACE(\u00a0--escape return properly\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0REPLACE(\u00a0--linefeed must be escaped\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0REPLACE(\u00a0--backslash too\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0REPLACE(COALESCE(b.c.value('text()[1]','NVARCHAR(MAX)'),''),--forwardslash\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'\\',\u00a0'\\\\'),\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'\/',\u00a0'\\\/'),\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CHAR(10),'\\n'),\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CHAR(13),'\\r'),\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CHAR(09),'\\t')\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0 +'\"'\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0x.a.nodes('*')\u00a0b(c)\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0FOR XML\u00a0PATH(''),TYPE).value('(.\/text())[1]','NVARCHAR(MAX)'),1,1,'')+'}'\r\n\u00a0\u00a0\u00a0FROM\u00a0@XMLResult.nodes('\/root\/*')\u00a0x(a)\r\n\u00a0\u00a0 )\u00a0JSON(theLine)\r\n\u00a0\u00a0FOR XML\u00a0PATH(''),TYPE).value('.','NVARCHAR(MAX)'\u00a0)\r\n,1,1,'')\r\nIF\u00a0@Rowcount>1\u00a0RETURN\u00a0'['+@JSONVersion+'\r\n]'\r\nRETURN\u00a0@JSONVersion\r\nEND\r\n<\/pre>\n
    You can alter the routine to give you different formats. Here is a bare-bones version that gives you a rather similar document to the old CSV format that I’ve described earlier in the article.<\/p>\n
    DECLARE @cars xml;\r\nSET @cars =\r\n'<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<root>\r\n\u00a0 <row>\r\n\u00a0\u00a0\u00a0 <Make>Volkswagen<\/Make>\r\n\u00a0\u00a0\u00a0 <Model>Eurovan<\/Model>\r\n\u00a0\u00a0\u00a0 <Year>2003<\/Year>\r\n\u00a0\u00a0\u00a0 <Color>White<\/Color>\r\n\u00a0\u00a0\u00a0 <Mileage>56,830<\/Mileage>\r\n\u00a0 <\/row>\r\n\u00a0 <row>\r\n\u00a0\u00a0\u00a0 <Make>Honda<\/Make>\r\n\u00a0\u00a0\u00a0 <Model>CRV<\/Model>\r\n\u00a0\u00a0\u00a0 <Year>2009<\/Year>\r\n\u00a0\u00a0\u00a0 <Color>Black<\/Color>\r\n\u00a0\u00a0\u00a0 <Mileage>35,600<\/Mileage>\r\n\u00a0 <\/row>\r\n<\/root>';\r\nSELECT \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '{\r\n\u00a0 \"columns\":['--start by creating a list of all the column headings, like the CSV header-line.\r\n\u00a0\u00a0 +Stuff((SELECT ', \"'+coalesce(x.a.value('local-name(.)', 'NVARCHAR(255)'),'')+'\"'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from @Cars.nodes('\/root[1]\/row[1]\/*') x(a) --get first row\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for xml path(''),TYPE).value('(.\/text())[1]','NVARCHAR(MAX)')\r\n\u00a0\u00a0 ,1,1,'')+'\r\n\u00a0 ],'--the end of the list of column headings\r\n+ '\r\n\u00a0 \"results\": ['--create a list (rows) of lists (tuples)\r\n\u00a0 +Stuff( --we want to snip out the leading comma\r\n\u00a0\u00a0\u00a0 (SELECT TheLine from --this is to glue each row into a string\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT ',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ['+ --this is the start of the row, representing the row object in the JSON list\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the local-name(.) is an eXPath function that gives you the name of the node\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Stuff((SELECT ',\"'+ b.c.value('text()[1]','NVARCHAR(MAX)') +'\"' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- 'text()[1]' gives you the text contained in the node\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from x.a.nodes('*') b(c) --get the row XML and split it into each node\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for xml path(''),TYPE).value('(.\/text())[1]','NVARCHAR(MAX)')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,1,1,'')+']'--remove the first comma \r\n\u00a0\u00a0\u00a0\u00a0 from @Cars.nodes('\/root\/*') x(a) --get every row\r\n\u00a0\u00a0\u00a0\u00a0 ) JSON(theLine) --each row \r\n\u00a0\u00a0\u00a0 for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )\r\n\u00a0 ,1,1,'')--remove the first leading comma\r\n\u00a0 +'\r\n\u00a0 ]\r\n}'\r\n<\/pre>\n
    {\r\n\u00a0 \"columns\":[ \"Make\", \"Model\", \"Year\", \"Color\", \"Mileage\"\r\n\u00a0 ],\r\n\u00a0 \"results\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [\"Volkswagen\",\"Eurovan\",\"2003\",\"White\",\"56,830\"],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [\"Honda\",\"CRV\",\"2009\",\"Black\",\"35,600\"]\r\n\u00a0 ]\r\n}\r\n<\/pre>\n
    More Complex JSON<\/h1>\n
    Sometimes, you don’t want flattened JSON. \u00a0Sometimes you have attributes, for example. You might have a more complex hierarchy. \u00a0The FOR XML PATH syntax can produce truly startling XML. Just to test our routine out though, we’ll \u00a0first take a classic XML example from the JSON documentation.<\/p>\n
    Declare @XMLSample XML\r\nSELECT @XMLSample='\r\n\u00a0 <glossary><title>example glossary<\/title>\r\n\u00a0 <GlossDiv><title>S<\/title>\r\n\u00a0\u00a0 <GlossList>\r\n\u00a0\u00a0\u00a0 <GlossEntry id=\"SGML\"\" SortAs=\"SGML\">\r\n\u00a0\u00a0\u00a0\u00a0 <GlossTerm>Standard Generalized Markup Language<\/GlossTerm>\r\n\u00a0\u00a0\u00a0\u00a0 <Acronym>SGML<\/Acronym>\r\n\u00a0\u00a0\u00a0\u00a0 <Abbrev>ISO 8879:1986<\/Abbrev>\r\n\u00a0\u00a0\u00a0\u00a0 <GlossDef>\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 <para>A meta-markup language, used to create markup languages such as DocBook.<\/para>\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 <GlossSeeAlso OtherTerm=\"GML\" \/>\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 <GlossSeeAlso OtherTerm=\"XML\" \/>\r\n\u00a0\u00a0\u00a0\u00a0 <\/GlossDef>\r\n\u00a0\u00a0\u00a0\u00a0 <GlossSee OtherTerm=\"markup\" \/>\r\n\u00a0\u00a0\u00a0 <\/GlossEntry>\r\n\u00a0\u00a0 <\/GlossList>\r\n\u00a0 <\/GlossDiv>\r\n\u00a0<\/glossary>'\r\n\r\nDECLARE @MyHierarchy Hierarchy -- to pass the hierarchy table around\r\ninsert into @MyHierarchy SELECT * from dbo.ParseXML(@XMLSample)\r\nSELECT dbo.ToJSON(@MyHierarchy)\r\n<\/pre>\n
    Which would give you<\/p>\n
    {\r\n\"title\" : \"example glossary\",\r\n\"GlossDiv\" :\u00a0\u00a0 {\r\n\u00a0 \"title\" : \"S\",\r\n\u00a0 \"GlossList\" :\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0 \"GlossEntry\" :\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \"GlossTerm\" : \"Standard Generalized Markup Language\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \"Acronym\" : \"SGML\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \"Abbrev\" : \"ISO 8879:1986\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \"GlossDef\" :\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"para\" : \"A meta-markup language, used to create markup languages such as DocBook.\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"GlossSeeAlso\" :\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"OtherTerm\" : \"GML\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 },\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"GlossSeeAlso\" :\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"OtherTerm\" : \"XML\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 },\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \"GlossSee\" :\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"OtherTerm\" : \"markup\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 },\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \"ID\" : \"SGML\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \"SortAs\" : \"SGML\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0 }\r\n}\r\n\r\n<\/pre>\n
    What we’ve done here is to convert the result into an intermediary SQL Table which is defined by a hierarchy type. Markup languages such as JSON and XML all represent object data as hierarchies. \u00a0We then pass it to a separate function,  ToJSON<\/b>, to render it as JSON.<\/p>\n
    The first trick is to represent it as a Adjacency list hierarchy in a table. This Adjacency list is really the Database equivalent of any of the nested data structures that are used for the interchange of serialized information with the application, and can be used to create XML, CSV, OSX Property lists, Python nested structures or YAML as easily as JSON.<\/p>\n
    Adjacency list tables have the same structure whatever the data in them. This means that you can define a single Table-Valued Type and pass data structures around between stored procedures. However, they are best held at arms-length from the data, since they are not relational tables, but something more like the dreaded EAV (Entity-Attribute-Value) tables. Converting the data from its Hierarchical table form will be different for each application, but is easy with a CTE.<\/p>\n
    It is unlikely that we’d have to produce JSON from raw XML as we did in the first example, but you might be faced by a SQL query like this, embedded in this SQL harness<\/p>\n
    DECLARE @MyHierarchy Hierarchy -- to pass the hierarchy table around\r\ninsert into @MyHierarchy SELECT * from dbo.ParseXML((\r\n--->The Query starts here\r\nSELECT ProductModelID AS \"@ProductModelID\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Name AS \"@ProductModelName\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT top 20 ProductID AS \"data()\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 AdventureWorks2012.Production.Product p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 p.ProductModelID = p.ProductModelID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH ('')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) AS \"@ProductIDs\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT top 10 Name AS \"ProductName\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 AdventureWorks2012.Production.Product pp\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 pp.ProductModelID = \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 pp.ProductModelID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH (''), type\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) AS \"ProductNames\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nFROM AdventureWorks2012.Production.ProductModel\r\nWHERE ProductModelID= 7 OR ProductModelID=9\r\nFOR XML PATH('ProductModelData')\r\n---> Query stops here\r\n))\r\nSELECT dbo.ToJSON(@MyHierarchy)\r\n<\/pre>\n
    … which gives this:<\/p>\n
    {\r\n\"ProductNames\" :\u00a0\u00a0 [\r\n\u00a0 \"HL Road Frame - Black, 58\",\r\n\u00a0 \"HL Road Frame - Red, 58\",\r\n\u00a0 \"Sport-100 Helmet, Red\",\r\n\u00a0 \"Sport-100 Helmet, Black\",\r\n\u00a0 \"Mountain Bike Socks, M\",\r\n\u00a0 \"Mountain Bike Socks, L\",\r\n\u00a0 \"Sport-100 Helmet, Blue\",\r\n\u00a0 \"AWC Logo Cap\",\r\n\u00a0 \"Long-Sleeve Logo Jersey, S\",\r\n\u00a0 \"Long-Sleeve Logo Jersey, M\"\r\n\u00a0 ],\r\n\"ProductModelID\" : 9,\r\n\"ProductModelName\" : \"LL Road Frame\",\r\n\"ProductIDs\" : \"680 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724\"\r\n}\r\n<\/pre>\n
    The code to do all this is attached to this article, but it is worth going through the ParseXML<\/code> function that takes an XML fragment or document and creates a SQL Hierarchy table from it. It can be used to inspect the contents of XML documents, of course, but it\u00a0 is primarily for interchange and for getting the contents of an XML table when you don’t know the structure up-front well-enough to use XPath, or if, like me, your brain isn’t quite big enough to understand the full expanse of XPath.<\/p>\n
    Basically, the routine starts by saving the base nodes (the root node if it is there) in a temporary table along with their name and then repeatedly extracting any element and attribute associated with it, and all its nested nodes until it has done them all. It then works out what sort of DataType each one is. It does all this simply by exploring the XML Structure without prior knowledge, using XPath\u00a0 wildcard syntax, and then using an XPath function ‘local-name()’ to determine the name of each node. It uses ‘text()[1]” to get the value, if any, associated with the node and also finds any attributes associated with the node. It does not make any difference between an attribute or element, since no other document format seems to care. Although I got a lot of inspiration for this routine from XMLTable<\/code> routine of \u00a0the great Jacob Sebastian\u00a0 (whose book on XSD should be on your bedside table) I didn’t follow the temptation to use a recursive CTE. No sir, iteration is good enough for me, and a lot quicker, it turns out! Jacob was the pioneer, and still wins the elegance prize for\u00a0 <\/code> XMLTable<\/code>, though.<\/p>\n
    \t\tCREATE TYPE dbo.Hierarchy AS TABLE\r\n\t\/*Markup languages such as JSON and XML all represent object data as hierarchies. Although it looks very different to the entity-relational model, it isn't. It is rather more a different perspective on the same model. The first trick is to represent it as a Adjacency list hierarchy in a table, and then use the contents of this table to update the database. This Adjacency list is really the Database equivalent of any of the nested data structures that are used for the interchange of serialized information with the application, and can be used to create XML, OSX Property lists, Python nested structures or YAML as easily as JSON.\r\n\t\r\n\tAdjacency list tables have the same structure whatever the data in them. This means that you can define a single Table-Valued\u00a0 Type and pass data structures around between stored procedures. However, they are best held at arms-length from the data, since they are not relational tables, but something more like the dreaded EAV (Entity-Attribute-Value) tables. Converting the data from its Hierarchical table form will be different for each application, but is easy with a CTE. You can, alternatively, convert the hierarchical table into XML and interrogate that with XQuery\r\n\t*\/\r\n\t(\r\n\t\u00a0\u00a0 element_id INT primary key, \/* internal surrogate primary key gives the order of parsing and the list order *\/\r\n\t\u00a0\u00a0 sequenceNo int NULL, \/* the place in the sequence for the element *\/\r\n\t\u00a0\u00a0 parent_ID INT,\/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document *\/\r\n\t\u00a0\u00a0 [Object_ID] INT,\/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here *\/\r\n\t\u00a0\u00a0 NAME NVARCHAR(2000),\/* the name of the object, null if it hasn't got one *\/\r\n\t\u00a0\u00a0 StringValue NVARCHAR(MAX) NOT NULL,\/*the string representation of the value of the element. *\/\r\n\t\u00a0\u00a0 ValueType VARCHAR(10) NOT null \/* the declared type of the value represented as a string in StringValue*\/\r\n\t)\r\n\tGo\r\n\t<\/pre>\n
    Now here is the code for the function that parses XML to fill a hierarchy table<\/p>\n
    IF OBJECT_ID (N'dbo.ParseXML') IS NOT NULL\r\n\u00a0\u00a0 DROP FUNCTION dbo.ParseXML\r\nGO\r\nCREATE FUNCTION dbo.ParseXML( @XML_Result XML)\r\n\/* \r\nReturns a hierarchy table from an XML document.\r\nAuthor: Phil Factor\r\nRevision: 1.2\r\ndate: 1 May 2014\r\nexample:\r\n\r\nDECLARE @MyHierarchy Hierarchy\r\nINSERT INTO @myHierarchy\r\nSELECT* from dbo.ParseXML((SELECT* from adventureworks.person.contact where contactID in (123,124,125) FOR XML path('contact'), root('contacts')))\r\nSELECTdbo.ToJSON(@MyHierarchy)\r\n\r\nDECLARE @MyHierarchy Hierarchy\r\nINSERT INTO @myHierarchy\r\nSELECT* from dbo.ParseXML('<root><CSV><item Year=\"1997\" Make=\"Ford\" Model=\"E350\" Description=\"ac, abs, moon\" Price=\"3000.00\" \/><item Year=\"1999\" Make=\"Chevy\" Model=\"Venture &quot;Extended Edition&quot;\" Description=\"\" Price=\"4900.00\" \/><item Year=\"1999\" Make=\"Chevy\" Model=\"Venture &quot;Extended Edition, Very Large&quot;\" Description=\"\" Price=\"5000.00\" \/><item Year=\"1996\" Make=\"Jeep\" Model=\"Grand Cherokee\" Description=\"MUST SELL!\r\nair, moon roof, loaded\" Price=\"4799.00\" \/><\/CSV><\/root>')\r\nSELECTdbo.ToJSON(@MyHierarchy)\r\n\r\n*\/\r\nRETURNS @Hierarchy TABLE\r\n\u00a0(\r\n\u00a0\u00a0\u00a0 Element_ID INT PRIMARY KEY, \/* internal surrogate primary key gives the order of parsing and the list order *\/\r\n\u00a0\u00a0\u00a0 SequenceNo INT NULL, \/* the sequence number in a list *\/\r\n\u00a0\u00a0\u00a0 Parent_ID INT,\/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document *\/\r\n\u00a0\u00a0\u00a0 [Object_ID] INT,\/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here *\/\r\n\u00a0\u00a0\u00a0 [Name] NVARCHAR(2000),\/* the name of the object *\/\r\n\u00a0\u00a0\u00a0 StringValue NVARCHAR(MAX) NOT NULL,\/*the string representation of the value of the element. *\/\r\n\u00a0\u00a0\u00a0 ValueType VARCHAR(10) NOT NULL \/* the declared type of the value represented as a string in StringValue*\/\r\n\u00a0)\r\n\u00a0\u00a0 AS \r\n\u00a0BEGIN\r\n\u00a0DECLARE\u00a0 @Insertions TABLE(\r\n\u00a0\u00a0\u00a0\u00a0 Element_ID INT IDENTITY PRIMARY KEY,\r\n\u00a0\u00a0\u00a0\u00a0 SequenceNo INT,\r\n\u00a0\u00a0\u00a0\u00a0 TheLevel INT,\r\n\u00a0\u00a0\u00a0\u00a0 Parent_ID INT,\r\n\u00a0\u00a0\u00a0\u00a0 [Object_ID] INT,\r\n\u00a0\u00a0\u00a0\u00a0 [Name] VARCHAR(50),\r\n\u00a0\u00a0\u00a0\u00a0 StringValue VARCHAR(MAX),\r\n\u00a0\u00a0\u00a0\u00a0 ValueType VARCHAR(10),\r\n\u00a0\u00a0\u00a0\u00a0 TheNextLevel XML,\r\n\u00a0\u00a0\u00a0\u00a0 ThisLevel XML)\r\n\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0DECLARE @RowCount INT, @ii INT\r\n\u00a0--get the base-level nodes into the table\r\n\u00a0INSERT INTO @Insertions (TheLevel, Parent_ID, [Object_ID], [Name], StringValue, SequenceNo, TheNextLevel, ThisLevel)\r\n\u00a0 SELECT\u00a0\u00a0 1 AS TheLevel, NULL AS Parent_ID, NULL AS [Object_ID], \r\n\u00a0\u00a0\u00a0 FirstLevel.value('local-name(.)', 'varchar(255)') AS [Name], --the name of the element\r\n\u00a0\u00a0\u00a0 FirstLevel.value('text()[1]','varchar(max)') AS StringValue,-- its value as a string\r\n\u00a0\u00a0\u00a0 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS SequenceNo,--the 'child number' (simple number sequence here)\r\n\u00a0\u00a0\u00a0 FirstLevel.query('*'), --The 'inner XML' of the current child\u00a0 \r\n\u00a0\u00a0\u00a0 FirstLevel.query('.')\u00a0 --the XML of the parent\r\n\u00a0 FROM @XML_Result.nodes('\/*') a(FirstLevel) --get all nodes from the XML\r\n\u00a0SELECT @RowCount=@@RowCount --we need this to work out if we are rendering an object or a list.\r\n\u00a0SELECT @ii=2\r\n\u00a0WHILE @RowCount>0 --while loop to avoid recursion.\r\n\u00a0 BEGIN\r\n\u00a0 INSERT INTO @Insertions (TheLevel, Parent_ID, [Object_ID], [Name], StringValue, SequenceNo, TheNextLevel, ThisLevel)\r\n\u00a0\u00a0 SELECT --all the elements first\r\n\u00a0\u00a0 @ii AS TheLevel, --(2 to the final level)\r\n\u00a0\u00a0\u00a0\u00a0 a.Element_ID, --the parent node\r\n\u00a0\u00a0\u00a0\u00a0 NULL, --we do this later. The object ID is merely a surrogate key to distinguish each node\r\n\u00a0\u00a0\u00a0\u00a0 [then].value('local-name(.)', 'varchar(255)') AS [name], --the name\r\n\u00a0\u00a0\u00a0\u00a0 [then].value('text()[1]','varchar(max)') AS [value], --the value\r\n\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER(PARTITION BY a.Element_ID ORDER BY (SELECT 1)),--the order in the sequence\r\n\u00a0\u00a0\u00a0\u00a0 [then].query('*'), --the 'inner' XML for the node\r\n\u00a0\u00a0\u00a0\u00a0 [then].query('.') --the XML from which this node was extracted\r\n\u00a0\u00a0 FROM\u00a0\u00a0 @Insertions a\r\n\u00a0\u00a0\u00a0\u00a0 CROSS apply a.TheNextLevel.nodes('*') whatsNext([then])\r\n\u00a0\u00a0 WHERE a.TheLevel = @ii - 1 --only look at the previous level\r\n\u00a0 UNION ALL -- to pick out the attributes of the preceding level\r\n\u00a0 SELECT @ii AS TheLevel,\r\n\u00a0\u00a0\u00a0\u00a0 a.Element_ID,--the parent node\r\n\u00a0\u00a0\u00a0\u00a0 NULL,--we do this later. The object ID is merely a surrogate key to distinguish each node\r\n\u00a0\u00a0\u00a0\u00a0 [then].value('local-name(.)', 'varchar(255)') AS [name], --the name\r\n\u00a0\u00a0\u00a0\u00a0 [then].value('.','varchar(max)') AS [value],--the value\r\n\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER(PARTITION BY a.Element_ID ORDER BY (SELECT 1)),--the order in the sequence\r\n\u00a0\u00a0 '' , ''--no nodes \r\n\u00a0\u00a0 FROM\u00a0\u00a0 @Insertions a\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0 CROSS apply a.ThisLevel.nodes('\/*\/@*') whatsNext([then])--just find the attributes\r\n\u00a0\u00a0 WHERE a.TheLevel = @ii - 1 OPTION (RECOMPILE)\r\n\u00a0 SELECT @RowCount=@@ROWCOUNT\r\n\u00a0 SELECT @ii=@ii+1\r\n\u00a0 END;\r\n\u00a0 --roughly type the DataTypes (no XSD available here) \r\n\u00a0UPDATE @Insertions SET\r\n\u00a0\u00a0\u00a0 [Object_ID]=CASE WHEN StringValue IS NULL THEN Element_ID \r\n\u00a0 ELSE NULL END,\r\n\u00a0\u00a0\u00a0 ValueType = CASE\r\n\u00a0\u00a0\u00a0\u00a0 WHEN StringValue IS NULL THEN 'object'\r\n\u00a0\u00a0\u00a0\u00a0 WHEN\u00a0 LEN(StringValue)=0 THEN 'string'\r\n\u00a0\u00a0\u00a0\u00a0 WHEN StringValue LIKE '%[^0-9.-]%' THEN 'string'\r\n\u00a0\u00a0\u00a0\u00a0 WHEN StringValue LIKE '[0-9]' THEN 'int'\r\n\u00a0\u00a0\u00a0\u00a0 WHEN RIGHT(StringValue, LEN(StringValue)-1) LIKE'%[^0-9.]%' THEN 'string'\r\n\u00a0\u00a0\u00a0\u00a0 WHEN\u00a0 StringValue LIKE'%[0-9][.][0-9]%' THEN 'real'\r\n\u00a0\u00a0\u00a0\u00a0 WHEN StringValue LIKE '%[^0-9]%' THEN 'string'\r\n\u00a0 ELSE 'int' END--and find the arrays\r\n\u00a0UPDATE @Insertions SET\r\n\u00a0\u00a0\u00a0 ValueType='array'\r\n\u00a0 WHERE Element_ID IN(\r\n\u00a0 SELECT candidates.Parent_ID \r\n\u00a0\u00a0 FROM\r\n\u00a0\u00a0 (\r\n\u00a0\u00a0 SELECT Parent_ID, COUNT(*) AS SameName \r\n\u00a0\u00a0\u00a0 FROM @Insertions --where they all have the same name (a sure sign)\r\n\u00a0\u00a0\u00a0 GROUP BY [Name],Parent_ID --no lists in XML\r\n\u00a0\u00a0\u00a0 HAVING COUNT(*)>1) candidates\r\n\u00a0\u00a0\u00a0\u00a0 INNER JOIN\u00a0 @Insertions insertions\r\n\u00a0\u00a0\u00a0\u00a0 ON candidates.Parent_ID= insertions.Parent_ID\r\n\u00a0\u00a0 GROUP BY candidates.Parent_ID \r\n\u00a0\u00a0 HAVING COUNT(*)=MIN(SameName))-- \r\n\u00a0INSERT INTO @Hierarchy (Element_ID,SequenceNo, Parent_ID, [Object_ID], [Name], StringValue,ValueType)\r\n\u00a0 SELECT Element_ID, SequenceNo, Parent_ID, [Object_ID], [Name], COALESCE(StringValue,''), ValueType\r\n\u00a0 FROM @Insertions--and insert them into the hierarchy.\r\n\u00a0RETURN\r\n\u00a0END\r\n<\/pre>\n
    You can, of course, use a routine like this to get XML into a relational format but it is much easier to use the XML VALUE()<\/code> method\u00a0 together with the NODE() <\/code>method to do this sort of work for you.<\/p>\n
    SELECT parent_ID,\r\n\u00a0 convert(varchar(20),Max(Case when Name='Make' then stringValue else '' end)) as [Make],\r\n\u00a0 convert(varchar(20),max(Case when Name='Model' then stringValue else '' end)) as [Model],\r\n\u00a0 convert(int,max(Case when Name='Year' then stringValue else '' end)) as [Year],\r\n\u00a0 convert(varchar(10),max(Case when Name='Color' then stringValue else '' end)) as [Color],\r\n\u00a0 convert(int,replace(max(Case when Name='Mileage' then stringValue else '' end),',','')) as [Mileage]\r\n\u00a0 from (SELECT Element_ID, SequenceNo, Parent_ID, [Object_ID],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Name, StringValue, Valuetype \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from dbo.ParseXML(\r\n'<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<root>\r\n\u00a0 <row>\r\n\u00a0\u00a0\u00a0 <Make>Volkswagen<\/Make>\r\n\u00a0\u00a0\u00a0 <Model>Eurovan<\/Model>\r\n\u00a0\u00a0\u00a0 <Year>2003<\/Year>\r\n\u00a0\u00a0\u00a0 <Color>White<\/Color>\r\n\u00a0\u00a0\u00a0 <Mileage>56,830<\/Mileage>\r\n\u00a0 <\/row>\r\n\u00a0 <row>\r\n\u00a0\u00a0\u00a0 <Make>Honda<\/Make>\r\n\u00a0\u00a0\u00a0 <Model>CRV<\/Model>\r\n\u00a0\u00a0\u00a0 <Year>2009<\/Year>\r\n\u00a0\u00a0\u00a0 <Color>Black<\/Color>\r\n\u00a0\u00a0\u00a0 <Mileage>35,600<\/Mileage>\r\n\u00a0 <\/row>\r\n<\/root>')) x(Element_ID, SequenceNo, \r\n\u00a0\u00a0 Parent_ID, [Object_ID], Name, StringValue, Valuetype)\r\nwhere parent_ID>1\r\ngroup by Parent_ID\u00a0\u00a0\u00a0 \r\n<\/pre>\n
    Which would give:<\/p>\n
    parent_ID\u00a0\u00a0 Make\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Model\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Year\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Color\u00a0\u00a0\u00a0\u00a0\u00a0 Mileage\r\n----------- --------------- --------------- ----------- ---------- -----------\r\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Volkswagen\u00a0\u00a0\u00a0\u00a0\u00a0 Eurovan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2003\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 White\u00a0\u00a0\u00a0\u00a0\u00a0 56830\r\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Honda\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CRV\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Black\u00a0\u00a0\u00a0\u00a0\u00a0 35600\r\n\r\n(2 row(s) affected)\r\n<\/pre>\n
    Conclusions<\/h1>\n
    If you can do so, it is nowadays often much easier for the application developer to deal with XML or JSON than with a SQL Result, particularly if the data required is hierarchical in nature. \u00a0If the volume of data isn’t great, then it should be possible to deliver this quickly without undue CPU burden on the server, thereby avoiding a separate conversion process within the application. \u00a0We already do this with XML so why not JSON, or any other format\u00a0 required?<\/p>\n
    SQL Server isn’t geared up for complex string manipulation, and isn’t therefore an obvious place to engage in this sort of activity. We need native JSON-integration in relational databases just as we already have XML-integration. After all, Sybase and PostgreSQL already have it so why not SQL Server?. Occasionally, the data\u00a0 as seen from the application perspective just isn’t table-based, and it is much easier, and sometimes quicker, \u00a0to deliver what the application requires in a format it can consume. \u00a0Whilst we await native JSON-integration, we must do the best we can with the tools that are available<\/p>\n
    Further Reading<\/h2>\n