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