SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML data from a column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hus
Starting Member

United Kingdom
4 Posts

Posted - 11/28/2012 :  15:52:18  Show Profile  Reply with Quote
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

United Kingdom
39 Posts

Posted - 11/28/2012 :  17:47:32  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000