Extract specific XML data from SQL Server using SQL

Question

I have XML like this, stored in an XML field in sql server:

<TableSpec
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns="custom_schema_name"
   ID="66912703-1201-457C-A37B-84D446B6C043"
   Name="Some name"
 >
  <Fields>
    <ForeignKeyField Name="SOMETABLEID" Required="true" ForeignTable="SOMETABLE"/>
    <GuidField Name="FIELD1" Required="true" />
    <DateField Name="FIELD2" Required="true" />
  </Fields>
</TableSpec>

I want to select the "Name" of every Field that is not a Foreign Key Field.

Given this example, I'd like my result set to be:

FIELD1
FIELD2

How can I do that?

Adam Haines · Accepted Answer · 2013-07-11 20:53:12Z

1

Here is some pseudo code. You will have to modify this for your specific sample.

Edit: I modified the code to reflect your namespace.

declare @x xml
set @x = 
'<TableSpec
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns="custom_schema_name"
   ID="66912703-1201-457C-A37B-84D446B6C043"
   Name="Some name">
  <Fields>
    <ForeignKeyField Name="SOMETABLEID" Required="true" ForeignTable="SOMETABLE"/>
    <GuidField Name="FIELD1" Required="true" />
    <DateField Name="FIELD2" Required="true" />
  </Fields>
</TableSpec>'

;WITH XMLNAMESPACES ('custom_schema_name' as custom_schema_name)
select
    x.i.value('@Name','varchar(256)'),
    x.i.value('local-name(.)','varchar(256)')
from @x.nodes('/custom_schema_name:TableSpec/custom_schema_name:Fields/*') x(i)
where x.i.value('local-name(.)','varchar(256)') <> 'ForeignKeyField'

edited Jul 11, 2013 at 20:53

answered Jul 11, 2013 at 20:30

Adam Haines

9205 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JosephStyons Over a year ago

That worked flawlessly. Thank you very much! When I have to query XML from SQL I feel like I'm trying to write a poem with a sponge - it just doesn't make sense.

Collectives™ on Stack Overflow

Extract specific XML data from SQL Server using SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related