asked on

sql server 2008 - SP to import any XML file into a new table

Hi..
I'm looking for code or SP that will take any XML file and import into a new table.
Simple XML file .. So I can just pass in the name and path of the XML file

Like Customers.XML and create and insert into a new CUSTOMERS table

any ideas? thx in advance

ASKER CERTIFIED SOLUTION

QuinnDex

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

aswathi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JElster

ASKER

What if i don't know the structure of the table and just want to import into a new table?
thanks

QuinnDex

you can create a table to match the xml from the xml file like this

declare @XML xml = 
'<root>
  <item id="1">
    <item_number>IT23</item_number>
    <title>Item number twenty-three</title>
    <setting>5 to 20</setting>
    <parameter>10 to 16</parameter>
    <new_node>data</new_node>
  </item>
  <item id="2">
    <item_number>RJ12</item_number>
    <title>Another item with a 12</title>
    <setting>7 to 35</setting>
    <parameter>1 to 34</parameter>
    <new_node>goes</new_node>
  </item>
  <item id="3">
    <item_number>LN90</item_number>
    <title>LN with 90</title>
    <setting>3 to 35</setting>
    <parameter>9 to 50</parameter>
    <new_node>here</new_node>
  </item>
</root>'

declare @SQL nvarchar(max) = ''
declare @Col nvarchar(max) = ', T.N.value(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]' 

select @SQL = @SQL + replace(@Col, '[COLNAME]', T.N.value('local-name(.)', 'sysname'))
from @XML.nodes('/root/item[1]/*') as T(N)

set @SQL = 'select '+stuff(@SQL, 1, 2, '')+' from @XML.nodes(''/root/item'') as T(N)'

Open in new window