Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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 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 this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

604 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