SQL XML - 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)

SQL XML

Author

Topic

IK1972

56 Posts

Posted - 2012-11-02 : 22:35:02

I have data in one xml column

<LTW>
<Arrtibute Level="PR" name="ST" type="Include" Value="'CA','NY'" />
<Arrtibute Level="LN" name="LA" type="LessThan" Value="500000" />
</LTW>

I want to create function who return table in this format.

Level name type Value
PR ST include 'CA','NY'
LN LA LessThan 500000

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-03 : 08:38:12

You can wrap the query below into a function. You may get better performance if you directly used the query against your table(s) instead of wrapping them in functions.

DECLARE @x XML = '<LTW> 
<Arrtibute Level="PR" name="ST" type="Include" Value="''CA'',''NY''" />
<Arrtibute Level="LN" name="LA" type="LessThan" Value="500000" /> 
</LTW>';

SELECT
	c.value('@Level','varchar(32)') [Level],
	c.value('@name','varchar(32)') [name],
	c.value('@type','varchar(32)') [type],
	c.value('@Value','varchar(32)') [Value]
FROM
	@x.nodes('/LTW/Arrtibute') T(c);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources