retrieve XML element names and values
-
I have an XML variable set like this:
declare @xmlSomeVariable xml
set @xmlSomeVariable='
<Parent>
<Child1>123</Child1>
<Child2>78</Child2>
<Child3>49212</Child3>
<Child4>21</Child4>
<Child5>45123</Child5>
<Child6>21</Child6>
<Child7>895</Child7>
</Parent>'
-- I am able to retrieve a single value from element Child1 like this:
SELECT
q.col.value ('Child1[1]','int') Child1
from @xmlSomeVariable.nodes('/Parent') as q(col);
but how can I retrieve a resultset of all elements and values like this:
Element ElementValue
-------- -------------
Child1 123
Child2 78
Child3 49212
.
.
Child7 895
-
SELECT q.col.value('local-name(.)','VARCHAR(10)') AS Element,q.col.value('.','VARCHAR(10)') AS ElementValue
FROM @xmlSomeVariable.nodes('/Parent/*') AS q(col)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you, that works very well
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply