Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stuck with an xml nodes query Expand / Collapse
Author
Message
Posted Monday, November 11, 2013 9:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:40 AM
Points: 24, Visits: 398
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 ?
Post #1513183
Posted Monday, November 11, 2013 9:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:40 AM
Points: 24, Visits: 398
Nevermind, I've figured it out.
Post #1513191
Posted Tuesday, November 12, 2013 8:27 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1513486
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse