0

Just how we are able to pull out the column names from our table with this code: 

select column_name from information_schema.columns
where table_name = 'theNameofMyTable'
order by ordinal_position

Is there a similar function to pull out the names of a XML file's column names? 

<Item>
    <Product>Food</Product>
    <Date>09/03/11</Date>
    <Shipped>10/01/11</Date> 
</Item>

I am wanting to write a code in sql that will pull out the column names in my xml files. I need to compare their schemas.

For this XML file, I'd want to see the column nodes: Product, Date, Shipped

crthompson
  • 15,653
  • 6
  • 58
  • 80
user3281388
  • 267
  • 1
  • 5
  • 20
  • Some db platforms have XML parsers built in. What platform are you using? – crthompson Jun 02 '14 at 19:30
  • Microsoft SQL Server Management Studio – user3281388 Jun 02 '14 at 19:33
  • I'm really stumped on how to approach this - do you think I should look into parsing? – user3281388 Jun 02 '14 at 19:34
  • Oh, I see, you have an `XML` file and you want to use `SQL` to parse it? – crthompson Jun 02 '14 at 19:38
  • The import wizard in SSMS can import XML, this would allow you to put your file into a table and then select the results out. Is that along the lines of what you're looking for? – crthompson Jun 02 '14 at 19:41
  • Sort of... I actually already have the xml in my sql. I have a large variety of very similar xmls that I am putting into a Table. I have been able to merge them however, I noticed the some of the xml have a slightly different schema than the others. Like for my example above, one xml might have an additional column . I'm trying to figure out a way to compare all of my xml columns to ensure I don't leave one out. Does that make sense? – user3281388 Jun 02 '14 at 19:49
  • I think you would find [this answer](http://stackoverflow.com/a/899351/2589202) helpful – crthompson Jun 02 '14 at 20:04

1 Answers1

2
declare @xml xml = '
<Item>
  <Product>Food</Product>
  <Date>09/03/11</Date>
  <Shipped>10/01/11</Shipped> 
</Item>'

select T.X.value('local-name(.)', 'nvarchar(128)')
from @xml.nodes('/Item/*') as T(X)

SQL Fiddle

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 1
    Thanks so much Mikael! This solved my problem! You're a genius! Really appreciate your help! I have marked this as the correct answer and given you the up arrow! :) – user3281388 Jun 02 '14 at 20:51