Improve company productivity with a Business Account.Sign Up

x
?
Solved

sql xquey

Posted on 2010-09-11
12
Medium Priority
?
371 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 61

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 61

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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for 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 61

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 61

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 61

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 61

Expert Comment

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

Best regards and happy coding,
Kevin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

606 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