Reading XML in SQL

[HoustonGuy]

I have a field that contains XML that I need to read values from.

I haven't found any good references for the basic task of reading a text field containing an XML string, and selecting certain nodes needed.

Any help is appreciated my brothers and sisters!

 

[gmmastros]

Can you post an example of the XML and the data you would like to get from it? Also, what is the data type of the column you are storing this in? Is it XML or varchar?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[yelworcm]

Try this series of articles. If these don't work, there is always BOL (Books OnLine)

http://www.sqlservercentral.com/stairway/92778/

 

[HoustonGuy]

Thanks for the quick responses - apologies for the delay.

The XML is stored in a text field, and is very large. Approximately 30,000 characters.
There are probably 30 - 40 Elements I will need to read values from.

Initially my problem is discovering the hierarchy. Is there a query/command that will allow me to open all nodes so I can review the XML in its proper format? After that, I can know the exact names of the nodes and proceed.

 

[yelworcm]

Easiest way I can think of would be to bcp out one of the values, and save that value as an .xml file. Then you can use a browser to examine the hierarchy.
I am not sure of the exact syntax, but this should get you started:

bcp queryout "select xmlfield from dbname..tablename where ID = 1" output.xml /c /Sservername /T

This would of course be run from a commandline.