[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Import XML into SQL Server 2008

Posted on 2014-01-18
20
Medium Priority
?
1,854 Views
Last Modified: 2014-02-10
Get XML FILE

Import into 2 Tables in :  SQL SERVER 2008

...............................

The First Problem : I am not good with XML files , I am good with SQL procs, views, functions, .... I don't mess with XML, but now I need to learn how

The Second Problem : I am having trouble with getting the XML data into a table using SQL Server
.
The Third Problem : I need to somehow LOOP the embedded envelope data [LineItemList] ... where there is always a [ChildCount] ... and then follows the [LineItems]

 
There is an ID number :  11213696
And there is an X-Number of Line Items

-      1. Import the Main Data into a Table :  
                o      [PurchReqNumb]
                o      [SolicitationNumb]
                o      [ReturnByDate]

-      2. Import the Line Items into a Second Table with a referencing data point
                o      Requires adding the [GovtRFQ  ID]  to the second table for a reference
                o      Add the Line Item details to fields in a second table

...Below is  pulled out of XML File ...
...I have attached the XML file in case you need to see it that way ...


<GovtRFQ ID="11213696">
       <Received>20131011 11:27</Received>
       <PurchReqNumb>00506802-AA</PurchReqNumb>
       <SolicitationNumb>SPM2DP-AA-Q-0005</SolicitationNumb>
       <ReturnByDate>10/15/2013</ReturnByDate>
       <LineItemList ChildCount="3">
            <LineItem CLIN="0001">
                  <NSN>6505002998015</NSN>
                  <Quantity>2</Quantity>
           </LineItem>
           <LineItem CLIN="0002">
                 <NSN>6505002998025</NSN>
                 <Quantity>1</Quantity>
           </LineItem>
           <LineItem CLIN="0003">
                 <NSN>6505002998035</NSN>
                 <Quantity>9</Quantity>
          </LineItem>
     </LineItemList>
</GovtRFQ>
ReqFileXML.xml
0
Comment
Question by:PAGANED
[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
  • 10
  • 5
  • 5
20 Comments
 
LVL 19

Expert Comment

by:zc2
ID: 39791094
Do you have the XSD Schema file to your XML ? if you do, you can use the XMLSQLBulkLoad component to import the XML to the SQL.
See examples here:
http://technet.microsoft.com/en-us/library/ms171806.aspx
0
 
LVL 1

Author Comment

by:PAGANED
ID: 39791150
I do not have an XSD file
.
Rather than use a VBScript , isnt' there some way to do this via a Procedure ..?
.
I was thinking of something like a SQL Procedure:
.
.
Create a Temp Table with an XML field
Import XML file text into that #TempTable.Field
Select that #Temp.Table.Field
Process by using INSERT INTO for SQL.Table1
Loop the Embedded groups using INSERT INTO for SQL.Table2
..
Process all GovtRFQs
.
.
0
 
LVL 19

Expert Comment

by:zc2
ID: 39791160
I'm sorry, no knowledge about SQL abilities to load XML. But I guess if there were any ones Microsoft would not bothered to create the additional component to do the job.

XSD schema could be crafted out of an existed XML example. It's not an easy task for a somebody who never deal with XML/XSD, but that's possible.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 1

Author Comment

by:PAGANED
ID: 39791182
This looks interesting :  If I was able to put everything into a single table then I could use different routines to get the data where I wanted
.
.

CREATE Temp TABLE #XMLtable
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML
)


INSERT INTO #XMLtable(XMLData)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(BULK 'C:\ReqFileXML.xml', SINGLE_BLOB) AS x;



DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM #XMLtable

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT PurchReqNumb, SolicitationNumb, ReturnByDate, ChildCount, CLIN, NSN, Quantity
FROM OPENXML(@hDoc, 'GovtRFQ/LineItemList')
WITH
(
PurchReqNumb [varchar](50) '../@PurchReqNumb',
SolicitationNumb [varchar](50) '../@SolicitationNumb',
ReturnByDate datetime '../@ReturnByDate',
ChildCount int '@ChildCount',
CLIN [varchar](10) '@CLIN',
NSN [varchar](50) '@NSN',
Quantity int '@Quantity'
)

EXEC sp_xml_removedocument @hDoc
GO
.
.
0
 
LVL 19

Expert Comment

by:zc2
ID: 39791219
This is great.
0
 
LVL 1

Author Comment

by:PAGANED
ID: 39794416
I am close but I can't get the data
I am doing something wrong
.
.

SELECT ID, PurchReqNumb, SolicitationNumb, ReturnByDate
FROM OPENXML(@hDoc, 'GovtRFQDocs/GovtRFQ')
WITH
(
ID int '@ID',
PurchReqNumb [varchar](50) '@PurchReqNumb',
SolicitationNumb [varchar](50) '@SolicitationNumb',
ReturnByDate datetime '@ReturnByDate'
)

11213696 : ID
NULL :   PurchReqNumb
NULL :   SolicitationNumb
NULL :   ReturnByDate
0
 
LVL 19

Assisted Solution

by:zc2
zc2 earned 1200 total points
ID: 39794436
Just a guess.
@PurchReqNumb means an attribute, but the PurchReqNumb is an element in the input XML. So, try to remove the '@' character.
0
 
LVL 1

Author Comment

by:PAGANED
ID: 39794462
It seems that I can NOT grab some of the data fields ..
..
frustrating
..
.

11213696  : ID
NULL :   PurchReqNumb
NULL :   SolicitationNumb
NULL :   ReturnByDate
  3      :  ChildCount
0001  :  CLIN
NULL  : NSN
NULL  :  Quantity

.



SELECT ID, PurchReqNumb, SolicitationNumb, ReturnByDate, ChildCount, CLIN, NSN, Quantity
FROM OPENXML(@hDoc, 'GovtRFQDocs/GovtRFQ/LineItemList/LineItem')
WITH
(
ID int '../../@ID',
PurchReqNumb [varchar](50) '../../@PurchReqNumb',
SolicitationNumb [varchar](50) '../../@SolicitationNumb',
ReturnByDate datetime '../../@ReturnByDate',
ChildCount int '../@ChildCount',
CLIN [varchar](10) '@CLIN',
NSN [varchar](50) '@NSN',
Quantity int '@Quantity'
)
0
 
LVL 19

Expert Comment

by:zc2
ID: 39794473
Why you did not try my suggestion?
0
 
LVL 1

Author Comment

by:PAGANED
ID: 39794479
YES ...  ZC2
.
.
Just a guess.
@PurchReqNumb means an attribute, but the PurchReqNumb is an element in the input XML. So, try to remove the '@' character.
.
.

ID      PurchReqNumb      SolicitationNumb      ReturnByDate      ChildCount      CLIN      NSN      Quantity
11213696      00506802-01      SPM2DP-14-Q-0003      10/15/2013      3      0001      6505002998095      2
11213696      00506802-01      SPM2DP-14-Q-0003      10/15/2013      3      0002      6505002998095      1
11213696      00506802-01      SPM2DP-14-Q-0003      10/15/2013      3      0003      6505002998095      9
.
.
0
 
LVL 1

Author Comment

by:PAGANED
ID: 39794835
I think this does it for this question  ...  ZC2
.
I will wait to close the question until I get a full XML file and see if I run into any other problems
.
.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39795027
Just a word of caution, OPENXML() is the old way to retrieve information from an Xml document.  With SQL Server 2005 and above you may find the Xml Data Type Methods a better option.
0
 
LVL 1

Accepted Solution

by:
PAGANED earned 0 total points
ID: 39795091
I am using Windows Server 2008
.
.
I would love to try another method of extracting the XML data
Could you recreate the following code using XQuery or some other SQL way ?

.
.
I am listing my currently working Code with OpenXML
.
.
       CREATE  TABLE #XMLtable
	(Id INT IDENTITY PRIMARY KEY,
	XMLData XML)

	INSERT INTO #XMLtable(XMLData)
	SELECT CONVERT(XML, BulkColumn) AS BulkColumn
	FROM OPENROWSET(BULK 'C:\ReqFileXML.xml', SINGLE_BLOB) AS x;

	DECLARE @XML AS XML, @hDoc AS INT
	SELECT @XML = XMLData FROM #XMLtable
	DROP Table #XMLtable

	EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

	SELECT ID, PurchReqNumb, SolicitationNumb, ReturnByDate, ChildCount, CLIN, NSN, Quantity ,CAGECode
	FROM OPENXML(@hDoc, 'GovtRFQDocs/GovtRFQ/LineItemList/LineItem')
	WITH 
	(
	ID int '../../@ID',
	PurchReqNumb [nvarchar](50) '../../PurchReqNumb',
	SolicitationNumb [nvarchar](50) '../../SolicitationNumb',
	ReturnByDate [nvarchar](50) '../../ReturnByDate',
	ChildCount int '../@ChildCount',
	CLIN [varchar](10) '@CLIN',
	NSN [varchar](50) 'NSN',
	Quantity [varchar](50) 'Quantity',
	CAGECode [nvarchar](10) '../../AppdPNList/AppdPNItem/CAGECode'
	)

	EXEC sp_xml_removedocument @hDoc

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 39795903
You could replace the sp_xml_preparedocument , OPENXML, sp_xml_removedocument  portion with this code:

SELECT	T.c.value('../../@ID[1]', 'integer') ID,
	T.c.value('../../PurchReqNumb[1]', 'nvarchar(50)') PurchReqNumb,
	T.c.value ('../../SolicitationNumb[1]', 'nvarchar(50)') SolicitationNumb ,
	T.c.value('../../ReturnByDate[1]', 'nvarchar(50)') ReturnByDate,
	T.c.value('../@ChildCount', 'integer'),
	T.c.value('@CLIN', 'varchar(10)') CLIN,
	T.c.value('NSN[1]', 'varchar(50)') NSN,
	T.c.value('Quantity[1]', 'varchar(50)') Quantity,
	T.c.value ('../../AppdPNList[1]/AppdPNItem[1]/CAGECode[1]', 'nvarchar(10)') CAGECode
FROM @Xml.nodes('GovtRFQDocs/GovtRFQ/LineItemList/LineItem') T(c)

Open in new window

0
 
LVL 1

Author Comment

by:PAGANED
ID: 39797070
Very Cool... thank you
.
Over the same XML data of 565 rows :

- OPENRECORDSET takes 1 Second

- XQuery NODES takes 10 seconds
.
That's a huge hit on performance based on such a small recordset
Do I need to activate something for better performance ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39797708
Do you mean OPENXML takes 1 second compared to Xml Data Type Methods taking 10 seconds?
0
 
LVL 1

Author Comment

by:PAGANED
ID: 39797759
The OPENXML code takes 1 second to run 565 rows
the NODES code takes 10 seconds to run 565 rows
.
I expected no difference in execution time, but there it was
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39798093
Just wanted to clarify as you had stated "OPENRECORDSET takes 1 Second".

If your Xml is that small, there is not much traffic on this server and speed is important to you I would stick with OPENXML.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39837614
using XQuery, which SHOULD BE newer and cooler, but all it is is newer since it's so slow.
I am afraid you are desperately missing the point.  Here is what I stated:
If your Xml is that small, there is not much traffic on this server and speed is important to you I would stick with OPENXML.
There is a reason OPENXML() in some circumstances is faster and there is also a reason why XQuery was introduced to replace it.
0
 
LVL 1

Author Closing Comment

by:PAGANED
ID: 39846829
I almost had it, and ZC2's comment about differences between Elements and Attributes while utilizing the @ character finally got good data returns as they should be
.
Anthony Perkins is using XQuery, which SHOULD BE newer and cooler, but all it is is newer since it's so slow. Anyway, it's another way to do the same thing I'm trying to do so thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

649 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