openxml vs xquery behind the scenes ?

Also, with regards the changes from Sql 2000 to Sql 2005.

When dealing with parsing/querying xml within a stored procedure, what is the difference (internally to sql server) between using OPENXML and xquery statements within a stored procedure ? With openxml, you need to call sp_xml_preparedocument, which will load a special version of the msxml parser called msxmlsql and loads the entire document into memory.

One thing that I like about XQUERY is that you can use the CROSS APPLY join to process database XML data on a row by row basis. I am new to using XML so I am also interested in hering the answer.

openxml performance

We do lot of xml parsing (OpenXML) and uses SQL Server 2k for our application. Recently we are getting the following error.
SEVERE: Exception in DB call com.jnetdirect.jsql.m: XML document could not
be created because server memory is low. Use sp_xml_removedocument to
release XML documents.
We have little more load on our server in the past few weeks. We found few SPs are missing "sp_xml_removedocument" and we added. In general are there any tips on using ? do any of the following helps?
making the xml size small by keeping the tags small etc., or
splitting larger xml documents into multiple documents and parsing
any others please let me know.
is there anyway to monitor the memory used by sql server for openxml?
Thanks.
Making the XML smaller might help a little but not a lot. Splitting the
file into smaller files might actually make things worse - there's some
overhead for each DOM created so having the same amount of data spread
around to more DOM's would use more memory. This may help if the smaller
size means the documents get processed enough faster so the total amount of
memory used at a time is reduced. The best thing to do is to reduce the
amount of time the DOM is in memory by rearranging the logic. There are
some cases where increasing memory to leave helps but I don't know if it
will help in your case.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"siddu" <siddu@.discussions.microsoft.com> wrote in message
news:637129A8-A7C5-4C1D-9D27-A9E75640D025@.microsoft.com...
> We do lot of xml parsing (OpenXML) and uses SQL Server 2k for our
> application. Recently we are getting the following error.
> SEVERE: Exception in DB call com.jnetdirect.jsql.m: XML document could not
> be created because server memory is low. Use sp_xml_removedocument to
> release XML documents.
> We have little more load on our server in the past few weeks. We found few
> SPs are missing "sp_xml_removedocument" and we added. In general are there
> any tips on using ? do any of the following helps?
> making the xml size small by keeping the tags small etc., or
> splitting larger xml documents into multiple documents and parsing
> any others please let me know.
> is there anyway to monitor the memory used by sql server for openxml?
> Thanks.
|||In addition to Roger's response:
Making sure that sp_xml_removedocument is always called is the most
important way to keep memory usage low.
The next step is to avoid having the DOM loaded. One way to achieve this is
to do all OpenXML calls as soon as possible (if needed loading data into a
temp table/table variable) and call sp_xml_removedocument early.
Best regards
Michael
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OyYRlTyWEHA.556@.tk2msftngp13.phx.gbl...
> Making the XML smaller might help a little but not a lot. Splitting the
> file into smaller files might actually make things worse - there's some
> overhead for each DOM created so having the same amount of data spread
> around to more DOM's would use more memory. This may help if the smaller
> size means the documents get processed enough faster so the total amount
> of memory used at a time is reduced. The best thing to do is to reduce
> the amount of time the DOM is in memory by rearranging the logic. There
> are some cases where increasing memory to leave helps but I don't know if
> it will help in your case.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "siddu" <siddu@.discussions.microsoft.com> wrote in message
> news:637129A8-A7C5-4C1D-9D27-A9E75640D025@.microsoft.com...
>

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