XML data into t-sql - 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

General SQL Server Forums

New to SQL Server Programming

XML data into t-sql

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-02-01 : 00:40:20

Hi,

I have a following XML structure:

<Field ID="130" Class="Normal" Name="GenderCode">
<Relations TableID="131" TableName="Codes">
<Filter FieldID="1" Type="C" Value="66" />
</Relations>
</Field>

And I would like to get following output in t-sql:

ID Name Value
------ -------------- -------
130 GenderCode 66

Thank you for tips and help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-01 : 07:16:29

If the XML is in a variable, you can simply do this:

DECLARE @x XML;
SET @x=
'<Field ID="130" Class="Normal" Name="GenderCode">
  <Relations TableID="131" TableName="Codes">
    <Filter FieldID="1" Type="C" Value="66" />
  </Relations>
</Field>';

SELECT
	c.value('@ID[1]','INT') [ID],
	c.value('@Name[1]','VARCHAR(32)') [Name],
	c.value('(Relations/Filter/@Value)[1]','INT') [Value]
FROM
	@x.nodes('./Field') T(c);

If your data is in a table, you will need to pick the column from the table and do a cross/outer apply against that column.

If you have many Relations nodes or Many Filter nodes, you would need to modify the query to account for that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources