April 14, 2004 at 8:54 pm
how to i parse this properly? I have this xml 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='c0' rs:name='CID #' rs:number='1' rs:nullable='true' rs:write='true'>
<s:datatype dt:type='float' dt:maxLength='8' rs:precision='15' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='c1' rs:name='Invoice #' rs:number='2' rs:nullable='true' rs:write='true'>
<s:datatype dt:type='float' dt:maxLength='8' rs:precision='15' rs:fixedlength='true'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row c0='100990' c1='2566446'/>
<z:row c0='101295' c1='2566457'/>
<z:row c0='103820' c1='2566554'/>
<z:row c0='106435' c1='2574458'/>
<z:row c0='106591' c1='2574463'/>
<z:row c0='106765' c1='2574471'/>
<z:row c0='107110' c1='2574483'/>
<z:row c0='107268' c1='2574485'/>
<z:row c0='107698' c1='2416136'/>
<z:row c0='107730' c1='2495031'/>
<z:row c0='108365' c1='883061'/>
</rs:data>
</xml>
how do i use the openxml? I am having nightmare trying to get the namespace to work.
Thanks a lot
April 15, 2004 at 11:46 pm
I tried this for awhile and was not able to get it either.. Had to resort to persisting without schema and in an element nature rather than attributes
April 15, 2004 at 11:58 pm
I have done this before and a quick look around has found what I was after. ENJOY!!
This is where I found the info - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/overcomingopenxmlhangupsListing_03.asp
Essentially sp_xml_preparedocument can take a 3rd parameter which is a declaration of the namespaces.
Here is the running code:
DECLARE
@xml_doc VARCHAR(4000),
@xml_handle INT
SET @xml_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="c0" rs:name="CID #" rs:number="1" rs:nullable="true" rs:write="true">
<s:datatype dt:type="float" dt:maxLength="8" rs:precision="15" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c1" rs:name="Invoice #" rs:number="2" rs:nullable="true" rs:write="true">
<s:datatype dt:type="float" dt:maxLength="8" rs:precision="15" rs:fixedlength="true"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row c0="100990" c1="2566446"/>
<z:row c0="101295" c1="2566457"/>
<z:row c0="103820" c1="2566554"/>
<z:row c0="106435" c1="2574458"/>
<z:row c0="106591" c1="2574463"/>
<z:row c0="106765" c1="2574471"/>
<z:row c0="107110" c1="2574483"/>
<z:row c0="107268" c1="2574485"/>
<z:row c0="107698" c1="2416136"/>
<z:row c0="107730" c1="2495031"/>
<z:row c0="108365" c1="883061"/>
</rs:data>
</xml>'
EXECUTE SP_XML_PREPAREDOCUMENT
@xml_handle OUTPUT,
@xml_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
c0 as CUSTOMER_CODE,
c1 as INVOICE_NUMBER
FROM OPENXML( @xml_handle, 'xml/rs:data/z:row', 0 )
WITH
(
c0 INT '@c0',
c1 INT '@c1'
) X
EXECUTE SP_XML_REMOVEDOCUMENT @xml_handle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply