Clever Geek Handbook

Selecting values ​​from XML to SQL

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

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

I need to select kunnr and kdgrp values ​​for each node client. I expect a result like this:

kunnr       kdgrp
1           2
2           2

What I have tried so far:

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

This results in one line with two columns containing XML:

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

One more attempt:

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 *'
+3


source to share


2 answers


Perhaps 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 request 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 the following output:

kunnr  kdgrp
1      2
2      2
+5


source


I am having a problem retrieving values ​​from T-SQL XML and encountered an issue that might help others. When getting data with :. value('(/root/subnode)[1]', 'varchar(max)')

this challenge will not retrieve the data, but the following call: .value('(//subnode)[1]', 'varchar(max)')

. Note that the working version replaced the root node with /. The problem with the first call was that the root node came with a xml namespace spec for example &< root xmlns="http://www..." &>

and to get a .value call to return the data I needed to pass the namespace spec which caused something to fail for some that's the reason.



+2


source






More articles:


All Articles