Reading XML Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Reading XML Data

Author

Topic

anderskd
Starting Member

25 Posts

Posted - 2010-02-24 : 12:13:31

I have a question regarding pulling some data out of XML files. I have to read in some XML invoice data, where there are header records with detailed item lines below the headers. I can load up the files and read in the header lines all at once, or the detail lines all at once, but am not sure how to combine this to see the data in one result set. This is probably confusing, so below is some simplified example data that I have setup.

------------ here is the XML data ------------------------
<INVCMessage>
<HEADER>
<InstanceID>XYZ</InstanceID>
<InstanceDate>02-01-2010</InstanceDate>
</HEADER>
<InvoiceData>
<VendorReference>VENDA</VendorReference>
<InvoiceID>1234</InvoiceID>
<Items>
<ItemData>
<ItemNumber>1</ItemNumber>
<ItemAmt>25.00</ItemAmt>
</ItemData>
<ItemData>
<ItemNumber>2</ItemNumber>
<ItemAmt>10.00</ItemAmt>
</ItemData>
</Items>
</InvoiceData>
<InvoiceData>
<VendorReference>VENDB</VendorReference>
<InvoiceID>5678</InvoiceID>
<Items>
<ItemData>
<ItemNumber>1</ItemNumber>
<ItemAmt>30.00</ItemAmt>
</ItemData>
</Items>
</InvoiceData>
</INVCMessage>

---- Here is the TSQL I use to read in the file ----------------
--READ IN XML
drop table #XmlImportTest

--load file
CREATE TABLE #XmlImportTest
(xmlFileName VARCHAR(300),
xml_data xml)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'D:\XML_TRAINING\APU_KA.XML'

-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO #XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')

GO

----- I use these to pull data -------------------------------
begin
declare @XMLdoc varchar(max)
declare @idoc int

select @XMLdoc = convert(varchar(max), xml_data) from #XmlImportTest
EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLdoc

--- This pulls the header data --------
select
VendorReference, InvoiceID
FROM OPENXML (@idoc, '/INVCMessage/InvoiceData',3)
WITH (
VendorReference varchar(50),
InvoiceID char(50)
)
end

--- This pulls the detailed data --------
select
ItemNumber, ItemAmt
FROM OPENXML (@idoc, '/INVCMessage/InvoiceData/Items/ItemData',3)
WITH (
ItemNumber varchar(50),
ItemAmt Decimal(18, 2)
)
end

--- Heres the question ----------------------------------
If the first query pulls the data like this:
VendorReference InvoiceID
--------------- ---------
VENDA 1234
VENDB 5678

(2 row(s) affected)

The second query pulls the data like this:
ItemNumber ItemAmt
---------- --------
1 25.00
2 10.00
1 30.00

(3 row(s) affected)

How would I query the data to combine the results so the data is shown in detail with the header information shown:
(doctored result set to see how I would like to pull data)

VendorReference InvoiceID ItemNumber ItemAmt
--------------- --------- ---------- ----------
VENDA 1234 1 25.00
VENDA 1234 2 10.00
VENDB 5678 1 30.00
(note the head records of InvoiceID 1234 would be repeated for the multiple items 1 and 2)

Hopefully my explanation isn't too confusing. I tried to simplify the data to show what I'm trying to do from a high level. I'm not a XML expert and can probably find a way to read this in line by line in Visual Basic if this can't be done, but I would assume there is some way to pull the data out like this from a query.

Thanks for any help on this!

Kelly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 12:25:25

you need to make use of new xml functions rather than OPENXML i guess

see


DECLARE @x xml
SET @x='<INVCMessage>
<HEADER>
<InstanceID>XYZ</InstanceID>
<InstanceDate>02-01-2010</InstanceDate>
</HEADER>
<InvoiceData>
<VendorReference>VENDA</VendorReference>
<InvoiceID>1234</InvoiceID>
<Items>
<ItemData>
<ItemNumber>1</ItemNumber>
<ItemAmt>25.00</ItemAmt>
</ItemData>
<ItemData>
<ItemNumber>2</ItemNumber>
<ItemAmt>10.00</ItemAmt>
</ItemData>
</Items>
</InvoiceData>
<InvoiceData>
<VendorReference>VENDB</VendorReference>
<InvoiceID>5678</InvoiceID>
<Items>
<ItemData>
<ItemNumber>1</ItemNumber>
<ItemAmt>30.00</ItemAmt>
</ItemData>
</Items>
</InvoiceData>
</INVCMessage>'

SELECT t.u.value('VendorReference[1]','varchar(100)') AS VendorReference,
t.u.value('InvoiceID[1]','int') as InvoiceID,
a.b.value('ItemNumber[1]','int') AS ItemNumber,
a.b.value('ItemAmt[1]','nUMERIC(10,2)') as  ItemAmt
FROM @x.nodes('/INVCMessage/InvoiceData')t(u)
CROSS APPLY u.nodes('Items/ItemData')a (b)

output
------------------------------------------
VendorReference	InvoiceID	ItemNumber	ItemAmt
VENDA	        1234	        1	        25.00
VENDA	        1234	        2	        10.00
VENDB	        5678	        1	        30.00

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anderskd
Starting Member

25 Posts

Posted - 2010-02-24 : 13:08:45

Hello visakh16,
Thanks for the reply. I was not familiar with the Cross Apply, but that is exactly what I was looking for! I will learn more about this and use it for this project.

Great response!

Kelly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:38:12

quote:
Originally posted by anderskd

Hello visakh16,
Thanks for the reply. I was not familiar with the Cross Apply, but that is exactly what I was looking for! I will learn more about this and use it for this project.

Great response!

Kelly

see what all you can do with apply operator
http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anderskd
Starting Member

25 Posts

Posted - 2010-02-25 : 11:32:08

Hey Visakh,
I read through your blog on the Apply operator and found it very interesting. The accounting software I work with now is still using 2000 - so I haven't had much exposure to this functionality yet, but am already thinking of areas where I can use it in the future. The examples are great - they are definetly going in my reference materials to be used again.

Thanks for sharing!

Kelly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 11:37:28

quote:
Originally posted by anderskd

Hey Visakh,
I read through your blog on the Apply operator and found it very interesting. The accounting software I work with now is still using 2000 - so I haven't had much exposure to this functionality yet, but am already thinking of areas where I can use it in the future. The examples are great - they are definetly going in my reference materials to be used again.

Thanks for sharing!

Kelly

You're welcome

you can subscribe to my blog if you find it useful

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources