perfectxml.com
 Basic Search  Advanced Search   
Topics Resources Free Library Software XML News About Us
home » info bank » Ask perfectxml.com Team Mon, Aug 13, 2007
I create XML document using ADO (adPersistXML). I would like to pass this XML to a stored procedure, and have SQL Server 2000 OPENXML function extract the information from it. In other words, I would like to have OPENXML read and parse ADO formatted XML. Can you please show an example of ADO XML with OPENXML?
Here is some sample T-SQL statements that use OPENXML with ADO formatted XML. Note how the root element is specified with the sp_xml_preparedocument (last parameter) this tells OPENXML about the namespaces used for XPath expression evaluation.
DECLARE @intDoc int

DECLARE @doc varchar(8000)

--ADO Formatted XML
SET @doc ='<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
     xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
     xmlns:rs="urn:schemas-microsoft-com:rowset"
     xmlns:z="#RowsetSchema">
        <s:Schema id="RowsetSchema">
     <s:ElementType name="row" content="eltOnly">
          <s:AttributeType name="Underlying_1" rs:number="1" rs:writeunknown="true">
               <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"
                rs:maybenull="false"/>
          </s:AttributeType>
          <s:AttributeType name="Underlying_1_Type" rs:number="2"
                rs:nullable="true" rs:writeunknown="true">
               <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
          </s:AttributeType>
          <s:AttributeType name="Underlying_2" rs:number="3" rs:writeunknown="true">
               <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"
                rs:maybenull="false"/>
          </s:AttributeType>
          <s:AttributeType name="Underlying_2_Type" rs:number="4"
                rs:nullable="true" rs:writeunknown="true">
               <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
          </s:AttributeType>
          <s:AttributeType name="value" rs:number="5" rs:nullable="true"
                rs:writeunknown="true">
               <s:datatype dt:type="float" dt:maxLength="8" rs:precision="15"
                rs:fixedlength="true"/>
          </s:AttributeType>
          <s:AttributeType name="mean" rs:number="6" rs:nullable="true"
                rs:writeunknown="true">
               <s:datatype dt:type="float" dt:maxLength="8" rs:precision="15"
                rs:fixedlength="true"/>
          </s:AttributeType>
          <s:AttributeType name="decay" rs:number="7" rs:nullable="true"
                rs:writeunknown="true">
               <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
                rs:scale="2" rs:precision="3" rs:fixedlength="true"/>
          </s:AttributeType>
          <s:extends type="rs:rowbase"/>
     </s:ElementType>
        </s:Schema>
        <rs:data>
     <z:row Underlying_1="AEX" Underlying_1_Type="Future"
           Underlying_2="AEX" Underlying_2_Type="Future" value="0.00108086429909611"
           mean="-2.4912099867497699E-3"/>
     <z:row Underlying_1="AEX" Underlying_1_Type="Future"
           Underlying_2="AI " Underlying_2_Type="Future" value="3.2859158631491701E-4"/>
     <z:row Underlying_1="AEX" Underlying_1_Type="Future"
           Underlying_2="AP" Underlying_2_Type="Future" value="-2.11713698585279E-5"/>
     <z:row Underlying_1="AEX" Underlying_1_Type="Future"
           Underlying_2="AQ " Underlying_2_Type="Future" value="1.7695555042260099E-4"/>
     <z:row Underlying_1="AEX" Underlying_1_Type="Future"
           Underlying_2="ARSUSD " Underlying_2_Type="Currency" value="-2.1665577633126301E-4"/>
     <z:row Underlying_1="AEX" Underlying_1_Type="Future"
           Underlying_2="AS" Underlying_2_Type="Future" value="-3.3872400188767999E-5"/>
     <z:row Underlying_1="AEX" Underlying_1_Type="Future"
           Underlying_2="AUDCHF" Underlying_2_Type="Currency" value="-3.0891045623447599E-5"/>
     <z:row Underlying_1="AEX" Underlying_1_Type="Future"
           Underlying_2="AUDGBP " Underlying_2_Type="Currency" value="-1.7817042130156401E-4"/>
</rs:data>
</xml>'

EXEC sp_xml_preparedocument 
     @intDoc OUTPUT, 
     @doc, 
     '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
     xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
     xmlns:rs="urn:schemas-microsoft-com:rowset"
     xmlns:z="#RowsetSchema" />'

SELECT * FROM OPENXML(@intDoc , '/xml/rs:data/z:row')
     WITH(     Underlying_1            varchar(8) '@Underlying_1',
          Underlying_1_Type        varchar(10) '@Underlying_1_Type',
          Underlying_2             varchar(8) '@Underlying_2',
          Underlying_2_Type       varchar(10) '@Underlying_2_Type',
          value              float '@value')

EXEC sp_xml_removedocument @intDoc 

  Contact Us | E-mail Us | Site Guide | About PerfectXML | Advertise ©2004 perfectxml.com. All rights reserved. | Privacy