Solved

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

Posted on 2013-11-22
4
442 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach 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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

810 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