OpenXML syntax help - 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)

OpenXML syntax help

Author

Topic

Hariharan
Starting Member

5 Posts

Posted - 2005-08-04 : 04:28:08

Hello experts

How should the OpenXML syntax be for the following XML?

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">
<cac:InvoiceLine>
<cac:ID>1</cac:ID>
<cbc:InvoicedQuantity quantityUnitCode="">1</cbc:InvoicedQuantity>
<cbc:LineExtensionAmount amountCurrencyID="SEK">12500.00</cbc:LineExtensionAmount>
<cac:Item>
<cbc:Description>Testprojekt Fas 1</cbc:Description>
<cac:SellersItemIdentification>
<cac:ID>999-5412</cac:ID>
</cac:SellersItemIdentification>
<cac:BasePrice>
<cbc:PriceAmount amountCurrencyID="SEK">12500.00</cbc:PriceAmount>
</cac:BasePrice>
</cac:Item>
</cac:InvoiceLine>
<cac:InvoiceLine>
<cac:ID>2</cac:ID>
<cbc:InvoicedQuantity quantityUnitCode="">1</cbc:InvoicedQuantity>
<cbc:LineExtensionAmount amountCurrencyID="SEK">2500.00</cbc:LineExtensionAmount>
<cac:Item>
<cbc:Description>Diverse omkostnader fas 1</cbc:Description>
<cac:SellersItemIdentification>
<cac:ID>999-5413</cac:ID>
</cac:SellersItemIdentification>
<cac:BasePrice>
<cbc:PriceAmount amountCurrencyID="SEK">2500.00</cbc:PriceAmount>
</cac:BasePrice>
</cac:Item>
</cac:InvoiceLine>
</Invoice>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

Expected resultset

ID InvoicedQuantity PriceAmount amountCurrencyID
999-5412 1 12500 SEK
999-5413 1 2500 SEK

Thanks
Hari

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources