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
|
