?
Solved

xml doc to mssql 2008 tables

Posted on 2013-01-23
9
Medium Priority
?
273 Views
Last Modified: 2013-02-01
I have an xml file that looks like this:

<?xml version="1.0" encoding="utf-8"?>
<table1>
  <row_for_table1>
    <t1_idcol>123</t1_idcol>
    <t1_col2>t1_col2 value</t1_col2>    
    <table2>
      <row_for_table2>
        <t2_col1>t2_col1 value</t2_col1>
        <t2_col2>t2_col2 value</t2_col2>      
        <row_for_table3>
          <table_3>
            <t3_col1>t3_col1 value</t3_col1>
            <t3_col2>t3_col2 value</t3_col2>            
          </table_3>
        </row_for_table3>
        <table4>
          <row_for_table4>
            <t4_col1>t4_col1 value</t4_col1>
            <t4_col2>t4_col2 value</t4_col2>
          </row_for_table4>
          <row_for_table4
            <t4_col1>t4_col1 value</t4_col1>
            <t4_col2>t4_col2 value</t4_col2>
          </row_for_table4>
          <row_for_table4>
            <t4_col1>t4_col1 value</t4_col1>
            <t4_col2>t4_col2 value</t4_col2>
          </row_for_table4>
        </table4>
      </row_for_table2>
      <row_for_table2>
        <t2_col1>t2_col1 value</t2_col1>
        <t2_col2>t2_col2 value</t2_col2>      
        <row_for_table3>
          <table_3>
            <t3_col1>t3_col1 value</t3_col1>
            <t3_col2>t3_col2 value</t3_col2>            
          </table_3>
        </row_for_table3>
        <table4>
          <row_for_table4>
            <t4_col1>t4_col1 value</t4_col1>
            <t4_col2>t4_col2 value</t4_col2>
          </row_for_table4>
          <row_for_table4
            <t4_col1>t4_col1 value</t4_col1>
            <t4_col2>t4_col2 value</t4_col2>
          </row_for_table4>
          <row_for_table4>
            <t4_col1>t4_col1 value</t4_col1>
            <t4_col2>t4_col2 value</t4_col2>
          </row_for_table4>
        </table4>
      </row_for_table2>
    </table2>
  </row_for_table1>
</table1>

My data structure is as follows:

CREATE TABLE [dbo].[table1](
      [idcol] [bigint] NOT NULL,
      [col2] [nvarchar](50) NOT NULL
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
      [idcol] ASC
))

CREATE TABLE [dbo].[table2](
      [idcol] [bigint] NOT NULL,
      [col1] [nvarchar](50) NOT NULL,
      [col2] [nvarchar](50) NOT NULL
 CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED
(
      [idcol] ASC
))

CREATE TABLE [dbo].[table3](
      [idcol] [bigint] NOT NULL,
      [col1] [nvarchar](50) NOT NULL,
      [col2] [nvarchar](50) NOT NULL
 CONSTRAINT [PK_table3] PRIMARY KEY CLUSTERED
(
      [idcol] ASC
))

CREATE TABLE [dbo].[table4](
      [idcol] [bigint] NOT NULL,
      [col1] [nvarchar](50) NOT NULL,
      [col2] [nvarchar](50) NOT NULL
 CONSTRAINT [PK_table4] PRIMARY KEY CLUSTERED
(
      [idcol] ASC
))

I am looking to pass my xml to an SP and add the appropriate rows to the proper tables.

Any pointers on a start?  We're using sql server 2008 r2
0
Comment
Question by:enjama
[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
9 Comments
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38813078
Follow below example ....you can have column with xml datatype in table and easily insert a xml doc in it..

USE tempdb
GO

IF EXISTS (
		SELECT 1
		FROM sys.objects
		WHERE NAME = 'test_table'
			AND type = N'U'
		)
	DROP TABLE test_table

CREATE TABLE test_table (
	seq INT identity(1, 1)
	,XML_DOC XML
	)
GO

--procedure to insert xml doc in test table 
CREATE PROCEDURE dbo.Insert_xmlDOc (@xml_DOC XML)
AS
BEGIN
	INSERT INTO test_table (XML_DOC)
	SELECT @xml_DOC
END
GO

DECLARE @x XML

SET @x = '<?xml version="1.0" encoding="utf-8"?>
<table1>
  <row_for_table1>
    <t1_idcol>123</t1_idcol>
    <t1_col2>t1_col2 value</t1_col2>    
    <table2>
      <row_for_table2>
        <t2_col1>t2_col1 value</t2_col1>
        <t2_col2>t2_col2 value</t2_col2>       
        <row_for_table3>
          <table_3>
            <t3_col1>t3_col1 value</t3_col1>
            <t3_col2>t3_col2 value</t3_col2>            
          </table_3>
        </row_for_table3>
      </row_for_table2>
      <row_for_table2>
        <t2_col1>t2_col1 value</t2_col1>
        <t2_col2>t2_col2 value</t2_col2>       
        <row_for_table3>
          <table_3>
            <t3_col1>t3_col1 value</t3_col1>
            <t3_col2>t3_col2 value</t3_col2>            
          </table_3>
        </row_for_table3>       
      </row_for_table2>
    </table2>
  </row_for_table1>
</table1>'

EXEC dbo.Insert_xmlDOc @xml_DOC = @x
GO

SELECT *
FROM test_table
GO

DROP PROCEDURE Insert_xmlDOc

Open in new window



Thanks,
saurabh
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38813111
I don't think the XML you've provided is formatted the way you've intended - there are rows for tables 3 and 4 nested inside rows for table 2 and 1, like they're somehow children. Unless the XML is properly laid out, it's going to be difficult to ensure that the data you're working with is easily parsed and ends up in the right place.

Once you've got properly formatted XML, I'd import it into a temp table and then work with it as demonstrated here:

http://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server

Effectively, you convert the XML back to a SQL table and then you can use that temp table to join to insert new rows into your existing tables, perform updates, or whatever else you want to do with them.
0
 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 38813740
check my blog for more details how to import from xml files

http://sqlservr.blog.com/2012/04/13/how-to-import-xml-file-into-sql-server/
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 38815176
You can try the SQLXMLBulkLoad component which is very fast compared to other methoses and very simple to use. The drawback would be that you need the component to me installed and you need to use an external script either in VBScript or Java script. You also need to create a mapping XDR schema(simpler than XSD) between your xml elements and the tables in the databse. The code is very simple though. You can see example here:

http://msdn.microsoft.com/en-us/library/ms171806.aspx

You could invoke the script from a job or even from a stored procedure with xp_cmdshell extended stored procedure.
0
 

Author Comment

by:enjama
ID: 38825381
Rows for Table 3 and 4 are nested on purpose.  They ARE children and have FK relationships to 1 and 2, as represented by the xml structure.

I don't really want to use a temp table, as the transaction volume is HIGH, up to 30 transactions  / sec.  I am going to dial up the xdr mapping solution in the morning and let you all know what I come up with..
0
 

Author Comment

by:enjama
ID: 38831332
SO, is there a way to load several tables with one xml doc ENTIRELY WITHIN SQL SERVER:?

In otherwords, pass the xml datatype to a USP with then querys the data and dumps it into tables?  Is that the solution?

Can I make it easier by using xsl withing the USP to break out the table values?

Does any of this make any sense?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38831844
Did you try the XDR mapping and load? The way it works is that in the XDR xml document you define the hierarchy of your XML document and the mapping of each to the SQL table. You place the XDR in the same folder with your document to simplify, or in one folder deeper called Mapping_Files.

You will have to have the tables created before you actually can execute the load. After that in the VBscript you will pass/provide the XML document and the XDR mapping document paths and execute it.
0
 

Author Closing Comment

by:enjama
ID: 38844855
Worked like a charm.  Thank you.  

FYI, I had to install biztalk server / VS 2010 to create my XDR (well, didnt HAVE to, but that made it alot easier) and it WORKS!

Once again - most elegant solution.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38845307
Glad to hear and good to know about VS that it can build the XDR from the xml file. I didn't know that, In my case I created it manually based on the example and I dealt with 4 level deep XML file hierarchy.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

764 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