interrogate XML string from database with T-SQL ?

[jendeacott]

Hi,

I have an application that stores well formatted XML in a varchar database field.

This works fine when I'm working with the data in c# as I can extract the field and interrogate it as an XML document using nodes etc.

My question is this, is it possible to interrogate the XML string in T-SQL?

For example looping through the nodes etc.

I know it is possible to retrieve fields from a table as XML using FOR XML, but this wont work for my problem as all the data is within one field.

Here's hoping I don’t have to wait for Yukon!!

Many thanks


Find out you can improve your plant security and plant tracking
¦

 

[JamesLean]

OPENXML can be used to return a rowset from an XML string. Here's a simple example:

DECLARE @xml varchar(1000),
	@hdoc int

--get an XML string (you could read this from a database)
SET @xml = '<xml><User><UserID>1</UserID><Name>User A</Name></User><User><UserID>2</UserID><Name>User B</Name></User></xml>'

--prepare an internal representation of the XML document
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

--use OPENXML to return the XML as a rowset
--the 2 parameter indicates the data is element-centric
SELECT *
FROM OPENXML(@hdoc, '/xml/User', 2)
WITH (
	UserID int,
	Name varchar(10)
)

--remove the internal representation
EXEC sp_xml_removedocument @hdoc

There is plenty of info on these XML functions/SPs in BOL.

--James

 

[jendeacott]

Thanks James,

I was searching though BOL but couldn't locate the solution.

OPENXML did the trick.

Cheers


Find out you can improve your plant security and plant tracking
¦