Solved

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

Posted on 2013-11-22
4
456 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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 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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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