Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql xquey

Posted on 2010-09-11
12
Medium Priority
?
361 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 1600 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 400 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1600 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

715 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