Howto query xml

Question

My source data in a SQL Server table has XML structured data. For each of the source rows I need to query the following sample XML structure in a way that I get a calculated value of all A sub elements. For example the SUM of bb and product of cc * dd over all A elements. The number of A elements in the structure is unknown.

<root>
 <A>
 <bb>bb1</bb>
 <cc>cc1</cc>
 <dd>dd1</dd>
 </A>
 <A>
 <bb>bb2</bb>
 <cc>cc2</cc>
 <dd>dd2</dd>
 </A>
 <A>
 <bb>bb3</bb>
 <cc>cc3</cc>
 <dd>dd3</dd>
 </A>
</root>

I experimented with something like this, but it looks like not being the right way:

SELECT Convert(xml,myXmlCol).value('(/root/A[1]/bb)[1]', 'int') as MyResult FROM SourceTable

Answer 1

The below query uses this sample data:

Declare @xml1 xml = CAST('<root>
 <A><bb>1</bb><cc>6</cc><dd>3</dd></A>
 <A><bb>2</bb><cc>5</cc><dd>6</dd></A>
 <A><bb>3</bb><cc>4</cc><dd>9</dd></A>
</root>' as xml);
Declare @xml2 xml = CAST('<root>
 <A><bb>4</bb><cc>3</cc><dd>7</dd></A>
 <A><bb>5</bb><cc>2</cc><dd>4</dd></A>
 <A><bb>6</bb><cc>1</cc><dd>1</dd></A>
</root>' as xml);
Declare @data TABLE(id int identity(0, 1), x xml)
Insert Into @data values(@xml1), (@xml2)

It declares 2 different variables and insert in in a dummy table. This is only a table with 2 rows:

0 <root><A><bb>1</bb><cc>6</cc><dd>3</dd></A>...</A></root>
1 <root><A><bb>4</bb><cc>3</cc><dd>7</dd></A>...</A></root>

Query:

SELECT d.id
 , xml.node.value('./bb[1]', 'varchar(50)') as colBB
 , xml.node.value('./cc[1]', 'varchar(50)') as colCC
 , xml.node.value('./dd[1]', 'varchar(50)') as colDD
FROM @data d
CROSS APPLY x.nodes('/root/A') as xml(node)

It read each row from the table and extract XML data.

Output:

id colBB colCC colDD
0 1 6 3
0 2 5 6
0 3 4 9
1 4 3 7
1 5 2 4
1 6 1 1

If can then be used with a GROUP BY:

SELECT id
 , SUM(colBB) as BB
 , SUM(colCC * colDD) as CC*DD
FROM (
 SELECT d.id
 , xml.node.value('./bb[1]', 'int') as colBB
 , xml.node.value('./cc[1]', 'int') as colCC
 , xml.node.value('./dd[1]', 'int') as colDD
 FROM @data d
 CROSS APPLY x.nodes('/root/A') as xml(node)
) x
GROUP BY id;

Output

id BB CC*DD
0 6 84
1 15 30