SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help with OpenXML
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hariharan
Starting Member

5 Posts

Posted - 08/01/2005 :  06:18:23  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 08/01/2005 :  09:56:03  Show Profile  Reply with Quote
"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
Go to Top of Page

Hariharan
Starting Member

5 Posts

Posted - 08/01/2005 :  10:35:36  Show Profile  Reply with Quote
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?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/01/2005 :  12:15:10  Show Profile  Reply with Quote
I thought you were going to ask that :-( ... sorry, I don't know.

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/01/2005 :  12:25:25  Show Profile  Reply with Quote
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
Go to Top of Page

Hariharan
Starting Member

5 Posts

Posted - 08/04/2005 :  03:43:07  Show Profile  Reply with Quote
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!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/04/2005 :  07:35:30  Show Profile  Reply with Quote
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
Go to Top of Page

Hariharan
Starting Member

5 Posts

Posted - 08/04/2005 :  10:34:53  Show Profile  Reply with Quote
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


Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/04/2005 :  10:55:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000