?
Solved

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

Posted on 2013-11-22
4
Medium Priority
?
460 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 1400 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 600 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
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: …

762 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