OpenXML help

• Hari-250426

  Old Hand

  Points: 378

  More actions

  August 4, 2005 at 3:41 am

  #90474

  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>

  <cbcescription>Testprojekt Fas 1</cbcescription>

  <cac:SellersItemIdentification>

  <cac:ID>999-5412</cac:ID>

  </cac:SellersItemIdentification>

  <cac:BasePrice>

  <cbcriceAmount amountCurrencyID="SEK">12500.00</cbcriceAmount>

  </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>

    <cbcescription>Diverse omkostnader fas 1</cbcescription>

   <cac:SellersItemIdentification>

    <cac:ID>999-5413</cac:ID>

    </cac:SellersItemIdentification>

   <cac:BasePrice>

    <cbcriceAmount amountCurrencyID="SEK">2500.00</cbcriceAmount>

    </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

• peterhe

  SSChampion

  Points: 11469

  More actions

  August 5, 2005 at 8:17 am

  #580019

  The following example is taken from http://www.perfectxml.com/Articles/XML/OPENXML.asp

  It shows how to handle namespace in OPENXML.

   

  DECLARE @idoc int

  DECLARE @doc varchar (1000)

  SET @doc ='

  <?xml version="1.0" encoding="UTF-8"?>

  <bk:Books xmlns:bk="http://www.PerfectXML.com">

  <bk:Book bk:ISBN="186100589X">

  <bk:Title>XML Application Development with MSXML 4.0</bk:Title>

  <bkublisher>Wrox Press</bkublisher>

  <bkateOfPurchase>2/1/2002</bkateOfPurchase>

  <Stores xmlns="BooksURI">

  Amazon, B&amp;N

  </Stores>

  </bk:Book>

  <bk:Book bk:ISBN="1861005466">

  <bk:Title>Professional SQL Server 2000 XML</bk:Title>

  <bkublisher>Wrox Press</bkublisher>

  <bkateOfPurchase>9/10/2001</bkateOfPurchase>

  <Stores xmlns="BooksURI">

  BookPool

  </Stores>

  </bk:Book>

  </bk:Books>

  '

  --Create an internal representation of the XML document

  -- Example 1: XML doc uses the Namespace prefix

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc,

     '<bk:Books xmlns:bk="http://www.PerfectXML.com" />'

  -- Execute a SELECT statement that uses the OPENXML rowset provider

  SELECT *

  FROM OPENXML (@idoc, '/bk:Books/bk:Book', 8)

  WITH

  (

  ISBN varchar(100) '@bk:ISBN',

  Title varchar(100) 'bk:Title',

  Publisher varchar(100) 'bkublisher',

  DateOfPurchase varchar(100) 'bkateOfPurchase'

  )

  -- Clear the XML document from memory

  EXEC sp_xml_removedocument @idoc

  ---------------------------------------

  --Create an internal representation of the XML document

  -- Example 2: XML Node uses the Default Namespace

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc,

     '<bk:Books xmlns:st="BooksURI" xmlns:bk="http://www.PerfectXML.com"/>'

  -- Execute a SELECT statement that uses the OPENXML rowset provider

  SELECT *

  FROM OPENXML (@idoc, '/bk:Books/bk:Book', 8)

  WITH

  (

  Stores varchar(100) 'st:Stores'

  )

  -- Clear the XML document from memory

  EXEC sp_xml_removedocument @idoc