[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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
0
enjama
Asked:
enjama
1 Solution
 
Saurabh BhadauriaCommented:
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
 
Ryan McCauleyCommented:
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
 
Ramesh Babu VavillaCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
ZberteocCommented:
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
 
enjamaAuthor Commented:
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
 
enjamaAuthor Commented:
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
 
ZberteocCommented:
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
 
enjamaAuthor Commented:
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
 
ZberteocCommented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now