XML data from a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

XML data from a column

Author

Topic

Hus
Starting Member

4 Posts

Posted - 2012-11-28 : 15:52:18

Hi All,

I have to retrieve XML data from a column where it is stored as text. I ideally want to split the text into the various columns

<v n="51500">
<InterpreterPresent s="e" a="sterlins" n="51500" />
<NameOfInterpreter s="e" a="sterlins" n="51500" />
</v>
<v n="52500">
<InterpreterPresent s="r" a="sterlins" n="52500" />
<NameOfInterpreter s="r" a="sterlins" n="52500" />
</v>

Results
column S , column A
e sterlins
r sterlins

Many thanks

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-28 : 17:47:32

Here is an example, assuming the data is in a variable - however, you will notice that it shows the values of s and a twice - because there are two nodes with those values. Which of those did you want to pick up? Change the XPATH from '/v/*' to '/v/InterpreterPresent' if you want to pick up from InterpreterPresent node.

DECLARE @x XML = '<v n="51500">
<InterpreterPresent s="e" a="sterlins" n="51500" /> 
<NameOfInterpreter s="e" a="sterlins" n="51500" /> 
</v>
<v n="52500">
<InterpreterPresent s="r" a="sterlins" n="52500" /> 
<NameOfInterpreter s="r" a="sterlins" n="52500" /> 
</v>';

SELECT
	c.value('local-name(.)','varchar(32)') AS NodeName,
	c.value('@s[1]','varchar(32)') AS S,
	c.value('@a[1]','varchar(32)') AS A
FROM
	@x.nodes('/v/*')T(c);

If the data is in a table, query is similar, except, change the from clause to

....
FROM
	tbl 
	CROSS APPLY colName.nodes('/v/*')T(c);

________________________________________
-- Yes, I am indeed a fictional character.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources