OpenXML with a schema

Why doesn't this work?
DECLARE @.xml VARCHAR(8000)
DECLARE @.iDoc INT
DECLARE @.Status INT
SELECT @.xml = '
<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" rs:updatable="true">
<s:AttributeType name="DocumentKey1" rs:number="1" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentKey1">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="DocumentKey2" rs:number="2" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentKey2">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="DocumentType" rs:number="3" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentType">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="SourceCompanyCode" rs:number="4" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="SourceCompanyCode">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="StarShipCompanyCode" rs:number="5"
rs:nullable="true" rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="StarShipCompanyCode">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<rs:insert>
<z:row DocumentKey1="172" SourceCompanyCode="SOA"/>
</rs:insert>
</rs:data>
</xml>
'
EXEC @.Status = sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT 'sp_xml_preparedocument status=',@.Status
--SELECT * FROM OPENXML (@.idoc, '/xml',2)
select *
FROM OPENXML (@.idoc, '/xml/data/insert/row',4)
WITH(
DocumentKey1 varchar(30)'@.DocumentKey1'
,DocumentKey2 varchar(30)'@.DocumentKey2'
,DocumentType varchar(30)'DocumentType'
,SourceCompanyCodevarchar(30) '@.SourceCompanyCode'
,StarShipCompanyCodevarchar(30) '@.StarShipCompanyCode'
)
You need to declare the namespaces in sp_xml_preparedocument, and use their
prefixes in the XPath used in the OPENXML function:
EXEC @.Status = sp_xml_preparedocument @.idoc OUTPUT, @.xml, '<root
xmlns:z="#RowsetSchema" xmlns:rs="urn:schemas-microsoft-com:rowset"/>'
SELECT 'sp_xml_preparedocument status=',@.Status
--SELECT * FROM OPENXML (@.idoc, '/xml',2)
select *
FROM OPENXML (@.idoc, '/xml/rs:data/rs:insert/z:row',2)
WITH (
DocumentKey1 varchar(30) '@.DocumentKey1'
,DocumentKey2 varchar(30) '@.DocumentKey2'
,DocumentType varchar(30) 'DocumentType'
,SourceCompanyCode varchar(30) '@.SourceCompanyCode'
,StarShipCompanyCode varchar(30) '@.StarShipCompanyCode'
)
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"John Grant" <johnwendell@.hotmail.com> wrote in message
news:B0134ADC-8D97-480C-A952-BA4862A7F75A@.microsoft.com...
Why doesn't this work?
DECLARE @.xml VARCHAR(8000)
DECLARE @.iDoc INT
DECLARE @.Status INT
SELECT @.xml = '
<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" rs:updatable="true">
<s:AttributeType name="DocumentKey1" rs:number="1" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentKey1">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="DocumentKey2" rs:number="2" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentKey2">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="DocumentType" rs:number="3" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentType">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="SourceCompanyCode" rs:number="4" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="SourceCompanyCode">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="StarShipCompanyCode" rs:number="5"
rs:nullable="true" rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="StarShipCompanyCode">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<rs:insert>
<z:row DocumentKey1="172" SourceCompanyCode="SOA"/>
</rs:insert>
</rs:data>
</xml>
'
EXEC @.Status = sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT 'sp_xml_preparedocument status=',@.Status
--SELECT * FROM OPENXML (@.idoc, '/xml',2)
select *
FROM OPENXML (@.idoc, '/xml/data/insert/row',4)
WITH (
DocumentKey1 varchar(30) '@.DocumentKey1'
,DocumentKey2 varchar(30) '@.DocumentKey2'
,DocumentType varchar(30) 'DocumentType'
,SourceCompanyCode varchar(30) '@.SourceCompanyCode'
,StarShipCompanyCode varchar(30) '@.StarShipCompanyCode'
)

OPENXML question

Can't get the following code to return two rows:
DECLARE @.data varchar(1000)
DECLARE @.hdoc int
select @.data = '<root><guid>a1</guid><guid>b1</guid></root>'
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.data
select *
from openxml(@.hdoc, '/root', 2)
with (guid varchar(10))
EXEC sp_xml_removedocument @.hdoc
Because you have only one root element you will only get one row.
Use
select *
from openxml(@.hdoc, '/root/guid', 2)
with (guid varchar(10) '.')
HTH
Michael
"Mark Frishman" <mfrishman_nospam@.hotmail.com> wrote in message
news:%23Bcjk7lfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Can't get the following code to return two rows:
> DECLARE @.data varchar(1000)
> DECLARE @.hdoc int
> select @.data = '<root><guid>a1</guid><guid>b1</guid></root>'
> EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.data
> select *
> from openxml(@.hdoc, '/root', 2)
> with (guid varchar(10))
> EXEC sp_xml_removedocument @.hdoc
>