Shred XML datatype to name/value pair

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Something like this

declare @x xml

set @x='

... your xml

'

select r.value('local-name(.)','varchar(20)') as name,

r.value('./text()[1]','varchar(20)') as value

from @x.nodes('//*') as x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

edo van vliet SSC Veteran Points: 271 More actions · Answer 2

Superduper! Exactly what I needed.

Points+1 🙂

-Edoode

edo van vliet SSC Veteran Points: 271 More actions · Answer 3

If I could bother you (all) again, what would be the xquery required to transform this XML fragment

<ArrayOfNameValuePair>

<NameValuePair>

<Name>totalCostDep</Name>

<Value>2357.81055</Value>

</NameValuePair>

<NameValuePair>

<Name>totalCost</Name>

<Value>1608.08594</Value>

</NameValuePair>

</ArrayOfNameValuePair>

into this..

name value

totalCostDep 2357.81055

totalCost 1608.08594

Thanks!!

-Edoode

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

select r.value('Name[1]','varchar(20)') as name,

r.value('Value[1]','decimal(12,6)') as value

from @x.nodes('/ArrayOfNameValuePair/NameValuePair') as x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum