Solved

sql xquey

Posted on 2010-09-11
12
306 Views
Last Modified: 2012-05-10
I need to search through an XML string for a specific node. How can I do this in SQL...I'm guessing Xquery?
0
Comment
Question by:felkamau
  • 6
  • 3
  • 3
12 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 total points
Comment Utility
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
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 100 total points
Comment Utility
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
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
@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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
No worries, @cyberkiwi, it happens. *smile*
0
 

Author Comment

by:felkamau
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
What errors are you getting?
Can you post one row of the varbinary data so we can try playing with it?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:felkamau
Comment Utility
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 400 total points
Comment Utility
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
 

Author Comment

by:felkamau
Comment Utility
that worked! I think I had the node id wrong.
thank you.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Ah, glad that helped.

Best regards and happy coding,
Kevin
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now