Select values from XML in SQL

Question

I need to select values from an XML document. These values are stored as childnodes as follows:

   <customers>
        <customer>
            <kunnr>1</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
        <customer>
            <kunnr>2</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
    </customers>

I need to select the values of kunnr and kdgrp for every customer node. I expect a result like this:

kunnr       kdgrp
1           2
2           2

What I tried so far:

SELECT  @xml.query('/customers/customer/kunnr') AS KUNNR,
        @xml.query('/customers/customer/kdgrp') AS KDGRP

This results in one row with two colums containing XML:

KUNNR                                     KDGRP
<kunnr>1</kunnr><kunnr>2</kunnr>          <kdgrp>2</kdgrp><kdgrp>2</kdgrp>

Another try:

SELECT  C.value('/kunnr/text()','nvarchar(10)') as KUNNR,
        C.value('/kdgrp/text()','nvarchar(10)') as KDGRP
from @xml.nodes('/customers/customer') AS T(C);

This resulted in the following error message:

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

adding a RDBMS e.g. SQL-Server might give your question more attraction. — bummi
– bummi, Commented Nov 18, 2014 at 8:12

Arion · Accepted Answer · 2014-11-18 08:13:31Z

5

Maybe something like this:

DECLARE @xml XML
SET @xml='<customers>
        <customer>
            <kunnr>1</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
        <customer>
            <kunnr>2</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
    </customers>'

And then a query like this:

SELECT
    c.value('kunnr[1]', 'nvarchar(10)') AS kunnr,
    c.value('kdgrp[1]', 'nvarchar(10)') AS kdgrp
FROM
    @xml.nodes('//customers/customer') as t(c)

This will give you this result:

kunnr  kdgrp
1      2
2      2

answered Nov 18, 2014 at 8:13

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Laren · Accepted Answer · 2015-03-31 18:46:57Z

2

I had a problem related to extracting values from T-SQL XML and found an issue that may help others. When retrieving data with: .value('(/root/subnode)[1]', 'varchar(max)') that call would not retrieve data but the following call did: .value('(//subnode)[1]', 'varchar(max)'). Note that the working version replaced the root node with a /. The problem with the first call seemed to be that the root node came with a specification of an xml namespace like &< root xmlns="http://www..." &> and to get the .value call to return data I needed to get past the specification of the namespace which was causing things to fail for some reason.

edited Mar 31, 2015 at 18:46

Mike Laren

8,23618 gold badges53 silver badges72 bronze badges

answered Mar 31, 2015 at 18:27

Kurt

211 bronze badge

1 Comment

GlennFromIowa Over a year ago

The parentheses did the trick for me. I didn't have a namespace, but I was trying this syntax: .value('node/subnode[1]', 'varchar(max)') and was getting the requires a singleton error above. Adding the parentheses around the path was the piece I was missing. So although the previous syntax did not work, both of these forms worked: .value('mynode[1]','varchar(max)') and .value('(node/subnode)[1]','varchar(max)') Hope this helps someone.

Collectives™ on Stack Overflow

Select values from XML in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related