Solved

xml doc to mssql 2008 tables

Posted on 2013-01-23
9
256 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now