CODEWITHSUNDEEP
sql-serverxmlxquery
Mike
Mike

Reputation: 109

Xquery requires a singleton :the column name is an attribute

DECLARE @XML XML
SET @xml = '
<root>
<a>
<b nm="DATE OF BIRTH"> 19871224</b> 
<b nm="DRIVER"> MIKE </b> 
</a>
<a>
<b nm="DATE OF BIRTH"> 19881124</b> 
<b nm="DRIVER"> TOM </b> 
</a>
<a>
<b nm="DATE OF BIRTH"> 19770601</b> 
<b nm="DRIVER"> DAVID </b> 
</a>
</root>'

select 
    pd.value('//b[@nm="DATE OF BIRTH"][1]','varchar(100)')
from 
    @xml.nodes('//a') as x(Rec)
cross apply 
    @xml.nodes('//a/b') as i(pd)

I want to parse the simple BY USING XQUERY. I always get error like this. I struggled with it for days. The reason is that the column name is an attribute. Thank you.

Msg 2389, Level 16, State 1, Line 23
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

The result I want to receive is 

DATE_OF_BIRTH     DRIVER
---------------------------
19871224           MIKE
19881124           TOM
19770601           DAVID

Upvotes: 3

Views: 5112

Answers (1)

marc_s
marc_s

Reputation: 755321

Try this:

select 
    Date_of_Birth = pd.value('(b[@nm="DATE OF BIRTH"])[1]', 'varchar(50)'),
    Driver = pd.value('(b[@nm="DRIVER"])[1]', 'varchar(50)')
from 
    @xml.nodes('/root/a') as i(pd)

Basically, you select all the <a> elements under <root> and for each of those elements, you grab the contained <b> element with either of the two @nm attributes to get the detailed info.

Produces the desired output for me.

Upvotes: 5

Related Questions