Solved

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

Posted on 2013-11-22
4
446 Views
Last Modified: 2013-11-25
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
0
Comment
Question by:JElster
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
QuinnDex earned 350 total points
ID: 39670377
try somthing like this


declare @table table
(
	Lastname varchar(20),
	FirstName varchar(20),
	MI char(2),
	Addr varchar(20),
	City varchar(20),
	State varchar(20),
	Zip int	 
)

declare @x xml
set @x='<Products>
 <Sales> 
 <LastName>Dummy</LastName> 
 <FirstName>Test</FirstName> 
 <MI>C</MI> 
 <Addr>1234 wonder city</Addr> 
 <City>wonder</City> 
 <State>FG</State> 
 <Zip>11111</Zip> 
 </Sales> 
<Sales> 
 <LastName>Tester</LastName> 
 <FirstName>Developer</FirstName> 
 <Addr>1234 wonder city</Addr> 
 <City>wonder</City> 
 <State>FG</State> 
 <Zip>11111</Zip>  
 </Sales>
 </Products>' 

insert @table(LastName,FirstName,MI,Addr,City,State,Zip)
select 
	x.value('LastName[1]','varchar(20)') as LastName,
	x.value('FirstName[1]','varchar(20)') as FirstName,
	x.value('MI[1]','char(2)') as M1,
	x.value('Addr[1]','varchar(20)') as Addr,
	x.value('City[1]','varchar(20)') as City,
	x.value('State[1]','varchar(20)') as State,
	x.value('Zip[1]','int') as ZIp
	from @x.nodes('/Products/Sales') e(x)


select * from @table

Open in new window

0
 
LVL 2

Assisted Solution

by:aswathi
aswathi earned 150 total points
ID: 39670949
Try this..

DECLARE @XmlCustomer XML      
SET @XmlCustomer =       '<ArrayOfCustomerDetailsXML>
                                          <CustomerXML>
                                                      <FirstName>  Cust1  </FirstName>                                                      
                                                      <Addr> Addr1 </Addr>                  
                                                      <Age>25 </Age>                                                                  
                                          </CustomerXML>
                                          <CustomerXML>
                                                      <FirstName>  Cust2  </FirstName>                                                      
                                                      <Addr> Addr2 </Addr>                  
                                                      <Age>30 </Age>                                                                  
                                          </CustomerXML>
                              </ArrayOfCustomerDetailsXML>'      
DECLARE @IDoc INT
DECLARE @CustDetails TABLE
                        (
                              ID INT NOT NULL IDENTITY(1, 1),
                              FirstName VARCHAR(20),                              
                              Addr VARCHAR(50),
                              Age INT
                        )
            EXEC sp_xml_preparedocument @IDoc OUTPUT, @XmlCustomer
                        INSERT INTO @CustDetails
                        (
                              FirstName, Addr, Age
                        )
                        SELECT
                              FirstName, Addr, Age
                        FROM OPENXML(@IDoc, '/ArrayOfCustomerDetailsXML/CustomerXML', 2)
                        WITH
                        (
                              FirstName VARCHAR(20),                              
                              Addr VARCHAR(50),
                              Age INT      
                        )      
                        
            EXEC sp_xml_removedocument @IDoc
                        
      SELECT * FROM @CustDetails
0
 
LVL 1

Author Comment

by:JElster
ID: 39671334
What if i don't know the structure of the table and just want to import into a new table?
thanks
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39672463
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

0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question