Help with OpenXML - 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 2000 Forums

SQL Server Development (2000)

Help with OpenXML

Author

Topic

Hariharan
Starting Member

5 Posts

Posted - 2005-08-01 : 06:18:23

Hello experts

I am getting null values as the result set for the following query. Any help will be highly appreciated.

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='<?xml version="1.0" encoding="ISO-8859-1" ?>
<Invoice
xmlns="urn:sfti:documents:BasicInvoice:1:0"
xmlns:xsi="http://www.w3.org2001XMLSchema-instance"
xmlns:udt="urn:oasis:names:tc:ubl:UnspecializedDatatypes:1:0"
xmlns:sdt="urn:oasis:names:tc:ubl:SpecializedDatatypes:1:0"
xmlns:cur="urn:oasis:names:tc:ubl:codelist:CurrencyCode:1:0"
xmlns:ccts="urn:oasis:names:tc:ubl:CoreComponentParameters:1:0"
xmlns:cbc="urn:oasis:names:tc:ubl:CommonBasicComponents:1:0"
xmlns:cac="urn:sfti:CommonAggregateComponents:1:0">
<ID>38280</ID>
<cbc:IssueDate>2005-05-23</cbc:IssueDate>
<InvoiceTypeCode>380</InvoiceTypeCode>
<InvoiceCurrencyCode>SEK</InvoiceCurrencyCode>
<TaxCurrencyCode>SEK</TaxCurrencyCode>
<LineItemCountNumeric>7</LineItemCountNumeric>
</Invoice>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc,'/Invoice',3)
WITH ( ID integer ,
InvoiceTypeCode varchar(8) ,
IssueDate datetime )

EXEC sp_xml_removedocument @idoc

Thanks
Hari

Kristen
Test

22859 Posts

Posted - 2005-08-01 : 09:56:03

"I am getting null values as the result set"

I get no rows, is that what you meant?

If so its the Name Space definitions in the <invoice> tag

Kristen

Hariharan
Starting Member

5 Posts

Posted - 2005-08-01 : 10:35:36

Thanks for your quick reply.
Infact it is a huge XML file with lot of invoice detials using namespaces. In this case how do I get the column 'IssueDate' in the resultset?

Kristen
Test

22859 Posts

Posted - 2005-08-01 : 12:15:10

I thought you were going to ask that :-( ... sorry, I don't know.

Kristen

Kristen
Test

22859 Posts

Posted - 2005-08-01 : 12:25:25

Actually, I have half an idea, but I don't know what to do with it

Stick

SELECT *
FROM OPENXML (@idoc,'/',3)

in your sample code and it will dump the data in edge table format. You might be able to deduce from that what SQL thinks the names of the various path elements are, and get it to cough up the data as you want it.

You might find a way to use paths on individual elements too - along the lines of

WITH
(
ID integer '/Invoice/@ID',
InvoiceTypeCode varchar(8) '/Invoice/@InvoiceTypeCode',
IssueDate datetime '/Invoice/@IssueDate'
)

Kristen

Hariharan
Starting Member

5 Posts

Posted - 2005-08-04 : 03:43:07

Thanks. The first part worked....putting into edge table.
But not the second part using the WITH clause.

Processing the edge table for entries that comes only once is fine. But when there are multiple entries (like item lines in an order) and if you need to have the order lines in a table format, then it seems quite an effort! Would be great if I can get help for the second sytax using the WITH clause!

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 07:35:30

I fiddled with it and got nowhere I'm afraid. I've never seen namespace stuff within the tags before ... but the Edge Resultset seems to handle it OK, and SQL takes the "cbc:" prefix into account.

So I reckon you need some special syntax for the WITH stuff that has N/S within it ... but I don't know how you find out what. I didn't spot anything of any real complexity in BoL that might have led me to an answer.

A hunt on Google might be worthwhile, I'll leave that shot to you!

I'll wander next door in a minute and just see if Ken Hederson has anything in his Bibles.

Kristen

Hariharan
Starting Member

5 Posts

Posted - 2005-08-04 : 10:34:53

Thanks for your help. I finally managed to get it work.
1):aa was missing in the first line (though it did not give any error when trying with the edge table)
2)namespaces should be defined in the sp_xml_preparedocument statement

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='<?xml version="1.0" encoding="ISO-8859-1" ?>
<Invoice
xmlns:aa="urn:sfti:documents:BasicInvoice:1:0"
xmlns:xsi="http://www.w3.org2001XMLSchema-instance"
xmlns:udt="urn:oasis:names:tc:ubl:UnspecializedDatatypes:1:0"
xmlns:sdt="urn:oasis:names:tc:ubl:SpecializedDatatypes:1:0"
xmlns:cur="urn:oasis:names:tc:ubl:codelist:CurrencyCode:1:0"
xmlns:ccts="urn:oasis:names:tc:ubl:CoreComponentParameters:1:0"
xmlns:cbc="urn:oasis:names:tc:ubl:CommonBasicComponents:1:0"
xmlns:cac="urn:sfti:CommonAggregateComponents:1:0">
<ID>38280</ID>
<cbc:IssueDate>2005-05-23</cbc:IssueDate>
<InvoiceTypeCode>380</InvoiceTypeCode>
<InvoiceCurrencyCode>SEK</InvoiceCurrencyCode>
<TaxCurrencyCode>SEK</TaxCurrencyCode>
<LineItemCountNumeric>7</LineItemCountNumeric>
</Invoice>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<Invoice xmlns:aa="urn:sfti:documents:BasicInvoice:1:0" xmlns:xsi="http://www.w3.org2001XMLSchema-instance" xmlns:udt="urn:oasis:names:tc:ubl:UnspecializedDatatypes:1:0" xmlns:sdt="urn:oasis:names:tc:ubl:SpecializedDatatypes:1:0" xmlns:cur="urn:oasis:names:tc:ubl:codelist:CurrencyCode:1:0" xmlns:ccts="urn:oasis:names:tc:ubl:CoreComponentParameters:1:0" xmlns:cbc="urn:oasis:names:tc:ubl:CommonBasicComponents:1:0" xmlns:cac="urn:sfti:CommonAggregateComponents:1:0"/>'

SELECT *
FROM OPENXML (@idoc,'/Invoice',3)
WITH ( ID integer ,
InvoiceTypeCode varchar(8) ,
IssueDate datetime '/Invoice/cbc:IssueDate')

EXEC sp_xml_removedocument @idoc

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 10:55:57

Excellent! Glad you got it working.

So BoL does mention it in sp_xml_preparedocument, but it's handling of namespaces is prefunctory, and the example is rather light. Easy to miss on that basis!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources