Iterating over JSON and XML values

One handy thing when working with JSON or XML is to take part of the data and represent it as tabular data, since everything in SQL Server works better as a table, pulling values out and representing them as a table can be handy.

Going back to our original data as shown below from my original blog post that kicked off JSON and XML month at SteveStedman.com we will be using the same XML and JSON. The goal here would be to pull out each employees first name from the XML and JSON and represent it as a table.

One option (the hard way) would be to use the JSON_VALUE and xmlData.Value functions to pull out each value, then join them into a table with a UNION ALL, somthing like this:

SELECT JSON_VALUE(JsonData, '$.employees[0].firstName') as FirstName
  FROM XmlAndJson
 WHERE id = 2

UNION ALL

SELECT JSON_VALUE(JsonData, '$.employees[1].firstName') as FirstName
  FROM XmlAndJson
 WHERE id = 2

UNION ALL

SELECT JSON_VALUE(JsonData, '$.employees[2].firstName') as FirstName
  FROM XmlAndJson
 WHERE id = 2
 ;



SELECT XmlData.value('(/employees/employee/firstName)[1]', 'varchar(max)') as FirstName
  FROM XmlAndJson
 WHERE id = 2
 
UNION ALL

SELECT XmlData.value('(/employees/employee/firstName)[2]', 'varchar(max)') as FirstName
  FROM XmlAndJson
 WHERE id = 2

UNION ALL

SELECT XmlData.value('(/employees/employee/firstName)[3]', 'varchar(max)') as FirstName
  FROM XmlAndJson
 WHERE id = 2
 ;

Which as I mention is the hard way, but it does produce the following results.

The results are what we are looking for in this specific example, but where they break down is when there are more employees represented in the XML, for each employee we need to add another UNION to bring the results together. That is not very iterative and since the title of this post includes the word iterating, we need to focus on how to do that.

Now we introduce the CROSS APPLY functionality that can be used like a JOIN to take a value from one result set (table) and apply it to a function that gets called once for each row. You can reference my JOIN TYPES poster for using CROSS APPLY. Something like this:

 -- JSON Example
SELECT id, JSON_VALUE(x.[value], '$.firstName') as firstName
  FROM XmlAndJson 
 CROSS APPLY OPENJSON(JSON_QUERY(JsonData, '$.employees')) AS x
 WHERE id = 2;

-- XML Exmaple
SELECT id, otherXml.value('(./text())[1]', 'VARCHAR(10)')  as firstName
  FROM XmlAndJson
 CROSS APPLY XmlData.nodes('/employees/employee/firstName') as MyDerivedTable (otherXml)
 WHERE id = 2;

Which produces similar results to the previous example, but with much more flexible code. Now we can try it with a bigger data set. Not big data, just bigger data.

GO -- bigger dataset
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>
  <employee>
    <firstName>Beth</firstName>
	<lastName>Allen</lastName>
  </employee>
  <employee>
    <firstName>John</firstName>
	<lastName>Jones</lastName>
  </employee>
  <employee>
    <firstName>Mary</firstName>
	<lastName>Smith</lastName>
  </employee>
</employees>';

DECLARE @JsonData as VARCHAR(MAX);
set @JsonData = '
{"employees":[
  { "firstName":"Steve", "lastName":"Stedman" },
  { "firstName":"Derrick", "lastName":"Bovenkamp" },
  { "firstName":"Bill", "lastName":"Simpkins" },
  { "firstName":"Beth", "lastName":"Allen" },
  { "firstName":"John", "lastName":"Jones" },
  { "firstName":"Mary", "lastName":"Smith" }
]}';


INSERT INTO XmlAndJson(XmlData, JsonData)
VALUES (@XmlData, @JsonData );

Then we run the same query to access the data, just changing the row id that we are looking for.

 -- JSON Example
SELECT id, JSON_VALUE(x.[value], '$.firstName') as firstName
  FROM XmlAndJson 
 CROSS APPLY OPENJSON(JSON_QUERY(JsonData, '$.employees')) AS x
 WHERE id = 11;

-- XML Exmaple
SELECT id, otherXml.value('(./text())[1]', 'VARCHAR(10)')  as firstName
  FROM XmlAndJson
 CROSS APPLY XmlData.nodes('/employees/employee/firstName') as MyDerivedTable (otherXml)
 WHERE id = 11;

From the results you can see we get all the employees first names back in this case.

So by using the cross apply we can effectively iterate over each row in a chunk of XML or JSON quickly and easily.

Iterating over JSON and XML values

Related Posts

Steps to add a SQL Server Sysadmin login

Tune in Tomorrow!

Leave a Reply Cancel reply