Solved

sql xquey

Posted on 2010-09-11
12
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33655101
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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 total points
ID: 33655121
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
ID: 33655175
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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33655179
@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 60

Expert Comment

by:Kevin Cross
ID: 33655185
No worries, @cyberkiwi, it happens. *smile*
0
 

Author Comment

by:felkamau
ID: 33676796
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
 
LVL 58

Expert Comment

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

Expert Comment

by:Kevin Cross
ID: 33676981
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
ID: 33676983
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 60

Accepted Solution

by:
Kevin Cross earned 400 total points
ID: 33677040
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
ID: 33677127
that worked! I think I had the node id wrong.
thank you.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33677360
Ah, glad that helped.

Best regards and happy coding,
Kevin
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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