Reading xml data

Reading xml data

DECLARE @MyXML XML

set @MyXML = @MyXML =

'<referenceranges><referencerange>

<property type="other" value="Male">

<lst gender="Male" value="4.0, 5.0">Negative </lst>

</property><property type="other" value="Male">

<btw gender="Male" value="4.0-6.0">Equivocal</btw>

</property><property type="other" value="Male">

<grq gender="Male" value="6.0">Positive </grq>

</property><property type="other" value="Female">

<lst gender="Female" value="4.0">Negative</lst>

</property><property type="other" value="Female">

<btw gender="Female" value="4.0-6.0">Equivocal</btw>

</property><property type="other" value="Female">

<grq gender="Female" value="6.0">Positive</grq>

</property>

</referencerange></referenceranges>'

 

B.test.value('@value[1]', 'nvarchar(400)') as Gender,

B.test.value('(lst/@value)[1]', 'nvarchar(400)') as lstvalue, 

B.test.query('lst').value('.', 'nvarchar(400)') as Lst,                            B.test.value('(btw/@value)[1]', 'nvarchar(400)') as btwvale,

B.test.query('btw').value('.', 'nvarchar(400)') as btw,    

B.test.value('(grq/@value)[1]', 'nvarchar(400)') as grqvalue,    

B.test.query('grq').value('.', 'nvarchar(400)') as grq    

FROM @MyXML.nodes('referenceranges/referencerange/property') AS B(test)

SELECT