Import XML into SQL Server 2008
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<
<SolicitationNumb>SPM2DP-A
<ReturnByDate>10/15/2013</
<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
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<
<SolicitationNumb>SPM2DP-A
<ReturnByDate>10/15/2013</
<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
ASKER
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
.
.
.
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
.
.
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.
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.
ASKER
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
.
.
.
.
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
.
.
This is great.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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/LineIt
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'
)
..
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/LineIt
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'
)
Why you did not try my suggestion?
ASKER
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
.
.
.
.
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
.
.
ASKER
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
.
.
.
I will wait to close the question until I get a full XML file and see if I run into any other problems
.
.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ?
.
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 ?
Do you mean OPENXML takes 1 second compared to Xml Data Type Methods taking 10 seconds?
ASKER
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
the NODES code takes 10 seconds to run 565 rows
.
I expected no difference in execution time, but there it was
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.
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.
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:
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.
ASKER
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!
.
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!
See examples here:
http://technet.microsoft.com/en-us/library/ms171806.aspx