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
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
Microsoft SQL Server 2008
Last Comment
QuinnDex
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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)'
thanks