Solved

xml doc to mssql 2008 tables

Posted on 2013-01-23
9
261 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
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:sqlservr
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 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 26

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 26

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 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