November 11, 2013 at 9:43 am
Hello there,
I'm currently working on extracting a load of xml elements out of a dynamic blob of xml. I have over 100 columns nailed down but 1 column from an element that I can't really reference has me stumped.
Below is an example of some xml that follows the same format that I'm trying to query.
<ROOTELEMENT>
<RESULTS Type="A" VALUE="1" />
<RESULTS Type="B" VALUE="2" />
<RESULTS Type="C" VALUE="3" />
<RESULTS Type="D" VALUE="4" />
<RESULTS Type="E" VALUE="5" />
<RESULTS Type="F" VALUE="6" />
<RESULTS Type="G" VALUE="7" />
</ROOTELEMENT>
What I need to do is pull out the value 4 from the Type D results element. The issue is that all of the elements within the rootelement are called results. The number of results can change and the position of the Type D results element can also change.
I'm using the .nodes .query method to pull out all the other values that I have required so far in one sql select statement, so I would like to stick with this method if at all possible.
Any one have any suggestions ?
November 11, 2013 at 9:57 am
Nevermind, I've figured it out.
November 12, 2013 at 8:27 am
Paul Treston (11/11/2013)
Hello there,I'm currently working on extracting a load of xml elements out of a dynamic blob of xml. I have over 100 columns nailed down but 1 column from an element that I can't really reference has me stumped.
Below is an example of some xml that follows the same format that I'm trying to query.
<ROOTELEMENT>
<RESULTS Type="A" VALUE="1" />
<RESULTS Type="B" VALUE="2" />
<RESULTS Type="C" VALUE="3" />
<RESULTS Type="D" VALUE="4" />
<RESULTS Type="E" VALUE="5" />
<RESULTS Type="F" VALUE="6" />
<RESULTS Type="G" VALUE="7" />
</ROOTELEMENT>
What I need to do is pull out the value 4 from the Type D results element. The issue is that all of the elements within the rootelement are called results. The number of results can change and the position of the Type D results element can also change.
I'm using the .nodes .query method to pull out all the other values that I have required so far in one sql select statement, so I would like to stick with this method if at all possible.
Any one have any suggestions ?
It's worth noting that the nodes method may not be the best choice for what you are doing. Nodes() allows you to query multiple nodes like so:
DECLARE @x xml='
<ROOTELEMENT>
<RESULTS Type="A" VALUE="1" />
<RESULTS Type="B" VALUE="2" />
<RESULTS Type="C" VALUE="3" />
<RESULTS Type="D" VALUE="4" />
<RESULTS Type="E" VALUE="5" />
<RESULTS Type="F" VALUE="6" />
<RESULTS Type="G" VALUE="7" />
</ROOTELEMENT>'
SELECT x.c.value('@VALUE','char(1)') AS val
FROM @x.nodes('//RESULTS') x(c)
For only one value (as is the case with what you are doing) you could use nodes like this:
SELECT x.c.value('@VALUE','char(1)') AS val
FROM @x.nodes('//RESULTS') x(c)
WHERE x.c.value('@Type','char(1)')='D'
Using the nodes() method you could read much less data and return the values much faster like this:
SELECT x.c.value('@VALUE','char(1)') AS val
FROM @x.nodes('/ROOTELEMENT/RESULTS[@Type="D"]') x(c)
That said, you don't even need nodes(), you could accomplish the same using just the query() or value() like so:
SELECT @x.value('(/ROOTELEMENT/RESULTS[@Type="D"]/@VALUE)[1]','int') AS val
SELECT @x.query('data(/ROOTELEMENT/RESULTS[@Type="D"]/@VALUE)[1]') AS val
Nodes is the most powerful of these methods but is generally the slowest. I suggest testing the value() and query() methods as well to see what works and performs best for you.
-- Itzik Ben-Gan 2001
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply