2

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
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.