felkamau
asked on
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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!
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!
No worries, @cyberkiwi, it happens. *smile*
ASKER
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" ]')
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"
What errors are you getting?
Can you post one row of the varbinary data so we can try playing with it?
Can you post one row of the varbinary data so we can try playing with it?
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.
Anyway, will help with this too.
ASKER
This is a small snippet of the XML
<SurveyID xmlns="">12345</SurveyID>
<SurveyDate xmlns="l">3/20/2009</Surve yDate>
<SubmitDate xmlns="">6/19/2009</Submit Date>
<Resurvey xmlns="l">true</Resurvey>
in this case I am looking for the SubmitDate.
<SurveyID xmlns="">12345</SurveyID>
<SurveyDate xmlns="l">3/20/2009</Surve
<SubmitDate xmlns="">6/19/2009</Submit
<Resurvey xmlns="l">true</Resurvey>
in this case I am looking for the SubmitDate.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that worked! I think I had the node id wrong.
thank you.
thank you.
Ah, glad that helped.
Best regards and happy coding,
Kevin
Best regards and happy coding,
Kevin
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