OPENXML Parsing Error: Multiple colons are not allow

I'm receiving the above parsing error for this document. I am trying to create an export that can be sent to a sister program and read into their database (same schemas). If I can export the entire entity (I do this using a union with nulls), I should
also be able to import it into another's database and maintain integrity. This is my attempt at the export half of it. I need to use OPENXML to be able to read the document once I receive it. This one is in non-binary format. The one I intend to use i
s binary, but binary is hard to debug so it is in this format. I have no idea what I am doing.
Thanks for any help.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
--Sample XML document
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" rs:updatable="true">
<s:AttributeType name="Tag" rs:number="1">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID" rs:autoincrement="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
<s:datatype dt:type="float" dt:maxLength="8" rs:precision="15" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="SLSMNID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="CusID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
</s:AttributeType>
<s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c15" rs:name="P!2!active" rs:number="16" rs:nullable="true">
<s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, "/>
<z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True"/>
<z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True" c16="394" c17="2759" c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
</rs:data>
</xml>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( QuoteID int '.',
QuoteID int '../@. QuoteID',
QuoteID int '../../@. QuoteID')
EXEC sp_xml_removedocument @.idoc
Regards,
Jamie
It is not multiple colons that are the problem but that your WITH clause
uses the same name for all the columns.
Try:
SELECT c1 as QuoteID, c2 as QuoteID, c3 as QuoteID
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( c1 int '.',
c2 int '../@.QuoteID',
c3 int '../../@.QuoteID')
Furthermore, you want your @.doc variable to be at least of size 4000. And
you probably want to use path expressions in the OpenXML that actually
extracts information from your XML.
I don't understand your comment about binary format. OpenXML requires XML
and not some binary format.
I hope now have a better idea of what you are doing :-)
HTH
Michael
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:4FFA87D1-077F-40AF-AE84-F2C1AA325BEE@.microsoft.com...
> I'm receiving the above parsing error for this document. I am trying to
> create an export that can be sent to a sister program and read into their
> database (same schemas). If I can export the entire entity (I do this
> using a union with nulls), I should also be able to import it into
> another's database and maintain integrity. This is my attempt at the
> export half of it. I need to use OPENXML to be able to read the document
> once I receive it. This one is in non-binary format. The one I intend to
> use is binary, but binary is hard to debug so it is in this format. I
> have no idea what I am doing.
> Thanks for any help.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> --Sample XML document
> 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" rs:updatable="true">
> <s:AttributeType name="Tag" rs:number="1">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3"
> rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID"
> rs:autoincrement="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
> <s:datatype dt:type="float" dt:maxLength="8" rs:precision="15"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="SLSMNID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="CusID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
> </s:AttributeType>
> <s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
> </s:AttributeType>
> <s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15"
> rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c15" rs:name="P!2!active" rs:number="16"
> rs:nullable="true">
> <s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:extends type="rs:rowbase"/>
> </s:ElementType>
> </s:Schema>
> <rs:data>
> <z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286"
> c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1
> electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity
> bin, "/>
> <z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True"/>
> <z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True" c16="394" c17="2759"
> c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
> </rs:data>
> </xml>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/xml/QUOTEID')
> WITH ( QuoteID int '.',
> QuoteID int '../@. QuoteID',
> QuoteID int '../../@. QuoteID')
> EXEC sp_xml_removedocument @.idoc
> --
> Regards,
> Jamie