• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

sql xquey

I need to search through an XML string for a specific node. How can I do this in SQL...I'm guessing Xquery?
0
felkamau
Asked:
felkamau
  • 6
  • 3
  • 3
3 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Yes, you would use XQuery support.

For example:

declare @xml xml;
set @xml = '
  John Smith42
  Jane Smith36
';

select @xml.query('//c[@id="name"]')

You can also get value using:

@xml.value({xpath}, {datatype})

Reference can be found here:

http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx
0
 
Kevin CrossChief Technology OfficerCommented:
Another example using value() and nodes():

declare @xml xml;
set @xml = '
  John Smith42
  Jane Smith36
';

select @xml.value('(//c[@id="name"])[1]', 'varchar(35)') name

select x.r.value('(./c[@id="name"])[1]', 'varchar(35)') name
from @xml.nodes('//t/r') x(r)
0
 
cyberkiwiCommented:
To state the obvious, have you given these crash courses a go?

http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx
http://www.stylusstudio.com/xquery_primer.html

Here's a very simple sample

-------------------------
declare @xml xml
set @xml = '<root>
<element id=''abc''>text1</element>
<element id=''ddd''>text2</element>
<element id=''eee''>text3</element>
<element id=''ggg''>text4</element>
</root>'

select @xml.value('(root/element[@id="ddd"])[1]', 'varchar(50)')

root/element is a path to the element nodes
@id="ddd" chooses the one(s) where the id attribute is "ddd"
the bracket and [1] is used to pick just the first match. this is required to use the value() function
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
cyberkiwiCommented:
@mwvisa1

This is so weird.. I had this open since 3 hours ago, came back and refreshed - no posts.
But I can now see your posts from more than 1/2 hr ago!
0
 
Kevin CrossChief Technology OfficerCommented:
No worries, @cyberkiwi, it happens. *smile*
0
 
felkamauAuthor Commented:
Here is what I am trying to do
The XML data is stored in a database as binary data
I would like to convert the data into XML and then read the node

see below:
declare @varBin varbinary(max);
declare @xml xml
set @varBin = (select XMLdata
from Table)

set @xml = cast(@varBin as varchar(max))

select @xml.query('//c[@id="name"]')
0
 
cyberkiwiCommented:
What errors are you getting?
Can you post one row of the varbinary data so we can try playing with it?
0
 
Kevin CrossChief Technology OfficerCommented:
Note this is related, but a little different than "I need to search through an XML string for a specific node." Not a big deal, just that the specific question you asked was answered and it is probably best to have had a question on converting your data to XML so that you could have added the necessary detail information that cyberkiwi is now asking for there.

Anyway, will help with this too.
0
 
felkamauAuthor Commented:
This is a small snippet of the XML

<SurveyID xmlns="">12345</SurveyID>
   <SurveyDate xmlns="l">3/20/2009</SurveyDate>
     <SubmitDate xmlns="">6/19/2009</SubmitDate>
        <Resurvey xmlns="l">true</Resurvey>

in this case I am looking for the SubmitDate.

0
 
Kevin CrossChief Technology OfficerCommented:
What didn't work with the code ?
declare @varBin varbinary(max);

set @varBin = convert(varbinary(max), '<SurveyID xmlns="">12345</SurveyID>
   <SurveyDate xmlns="l">3/20/2009</SurveyDate>
     <SubmitDate xmlns="">6/19/2009</SubmitDate>
        <Resurvey xmlns="l">true</Resurvey>');

select convert(xml, @varBin).query('//SubmitDate');

Open in new window

0
 
felkamauAuthor Commented:
that worked! I think I had the node id wrong.
thank you.
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, glad that helped.

Best regards and happy coding,
Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now