Link to home
Start Free TrialLog in
Avatar of enjama
enjama

asked on

xml doc to mssql 2008 tables

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
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

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
Avatar of Ryan McCauley
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.
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of enjama
enjama

ASKER

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..
Avatar of enjama

ASKER

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?
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.
Avatar of enjama

ASKER

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.
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.