Alternative to T-SQL OpenXML

Hi,

Our application makes extensive use of XML to move data between ASP.NET and the backend SQL Server 2000 database. Lately we have found that the OpenXML statement is becoming a major performance bottleneck.

Could someone suggest a more efficient way of 1) passing data between the web and data tier or 2) alternatives to OpenXml/sp_prepare_document.

Regards
Arjun

Dear Arjun

First, how does your OpenXML code look like? There are some best practises that can have a large performance impact.

Secondly, are you using Service pack 4? That also has some sometimes substatial performance improvements.

Otherwise, I would recommend at looking at the SQLXML 3.0 XML Bulkload object, or a ADO.Net DataSet based approach.

Best regards

Michael

|||

Dear Michael,

Here's a code snippet. This is contained in a stored procedure which accepts the XML data as input. Any suggestions to tune this.

exec sp_xml_preparedocument @.outputDoc OUTPUT, @.strAddXML

Insert into @.Table(txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev )
Select txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev FROM OPENXML(@.outputDoc, 'DocXML/intRow',1)
with
(
txtCategory varchar(50),
intFinYearEnd int,
fltTotalRevenue float,
fltCost float,
fltCostPer float,
fltCommPer float,
fltAprRev float,
fltMayRev float,
fltJunRev float,
fltJulRev float,
fltAugRev float,
fltSepRev float,
fltOctRev float,
fltNovRev float,
fltDecRev float,
fltJanRev float,
fltFebRev float,
fltMarRev float
)

Regards
Arjun

|||

Hi Arjun

Sorry for the late reply but I was travelling.

The OpenXML statement that you show looks ok to me. Have you done some performance testing to see where the time is being spent? During parsing of the sp_xml_preparedocument or the actual OpenXML call?

How large is the XML that you are passing in? Note that SQL Server 2000 versions prior to SP4 used a parser that had some non-linear scaling problems during parsing of larger documents (which has been fixed with SP4). What service pack version are you using?

Best regards

Michael

Alternative to T-SQL OpenXML

Hi,

Our application makes extensive use of XML to move data between ASP.NET and the backend SQL Server 2000 database. Lately we have found that the OpenXML statement is becoming a major performance bottleneck.

Could someone suggest a more efficient way of 1) passing data between the web and data tier or 2) alternatives to OpenXml/sp_prepare_document.

Regards
Arjun

Dear Arjun

First, how does your OpenXML code look like? There are some best practises that can have a large performance impact.

Secondly, are you using Service pack 4? That also has some sometimes substatial performance improvements.

Otherwise, I would recommend at looking at the SQLXML 3.0 XML Bulkload object, or a ADO.Net DataSet based approach.

Best regards

Michael

|||

Dear Michael,

Here's a code snippet. This is contained in a stored procedure which accepts the XML data as input. Any suggestions to tune this.

exec sp_xml_preparedocument @.outputDoc OUTPUT, @.strAddXML

Insert into @.Table(txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev )
Select txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev FROM OPENXML(@.outputDoc, 'DocXML/intRow',1)
with
(
txtCategory varchar(50),
intFinYearEnd int,
fltTotalRevenue float,
fltCost float,
fltCostPer float,
fltCommPer float,
fltAprRev float,
fltMayRev float,
fltJunRev float,
fltJulRev float,
fltAugRev float,
fltSepRev float,
fltOctRev float,
fltNovRev float,
fltDecRev float,
fltJanRev float,
fltFebRev float,
fltMarRev float
)

Regards
Arjun

|||

Hi Arjun

Sorry for the late reply but I was travelling.

The OpenXML statement that you show looks ok to me. Have you done some performance testing to see where the time is being spent? During parsing of the sp_xml_preparedocument or the actual OpenXML call?

How large is the XML that you are passing in? Note that SQL Server 2000 versions prior to SP4 used a parser that had some non-linear scaling problems during parsing of larger documents (which has been fixed with SP4). What service pack version are you using?

Best regards

Michael

Alternative to OpenXML

Hi,
I want to enter data in xml into tables in the database.
Howevere, I do not want to use OpenXML
Please let me know if there is an alternative.
Regards,
Shilpa
hi Shilpa,
Shilpa wrote:
> Hi,
> I want to enter data in xml into tables in the database.
> Howevere, I do not want to use OpenXML
> Please let me know if there is an alternative.
> Regards,
> Shilpa
OpenXML is a buuilt-in function to return an XML Document as a rowset, thus
returning it in a relational way, and the ourput will be rows and columns...
depending on your schema, you do not need it to insert XML documents into
your database if you store an entire XML document in an (n)varchar or
(n)text column...
but if you have a relational schema modelled "the old way", where 1st normal
form excluded hyerarchical data as: "a relvar in in 1st if and only if, in
every legal value of that relvar, every tuple contains exactly one value for
each attribute" (C.J. Date, "An introduction to Database Systems", Addison
Wesley, p. 358), then this is a way to feed an INSERT INTO statement with an
XML document to shred into relational model like
INSERT INTO some_object (<col_list>)
SELECT <col_list>
FROM OPENXML(@.xmlDoc, 'XPath query', n_flags)
WITH (<mappings>);
the XML parser is then loaded and executes the XPath query, returnig the
required results that will be "translated" by SQL Server engine into
relational data to feed the INSERT INTO statement...
if you can/want not use this feature, you can always resort on client
processing, thus traversing as desired the XML Document in your client
application(s) (that can even be hosted and executed on the same server as
SQL Server) via your preferred DOM or SAX parser, extracting the node's
values (and eventually pre-processing them) in order to feed a stored
procedure to insert new data (or dynamic client SQL insert statements, but
storde procedure use should be preferred)...
another way is the use of XML Bulk Load component, part of the Web Release 1
of SQL Server 2000,
http://msdn.microsoft.com/library/de...kload_7pv0.asp
SQL Server 2005 based engines, and thus SQLExpress, include a new data type
explicitely dedicated for XML data, the new XML datatype,
http://msdn2.microsoft.com/en-us/library/ms189887.aspx, where everything
should be easely managed..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Alternative to OpenXML

Hi,
I want to entere data in xml into tables in the database.
Howevere, I do not want to use OpenXML
Please let me know if there is an alternative.
Regards,
ShilpaWhat product are you using? XML is natively supported in SQL 2005. What
exactly are you trying to achieve?
ML
http://milambda.blogspot.com/

Alternative to OpenXML

Hi,
I want to entere data in xml into tables in the database.
Howevere, I do not want to use OpenXML
Please let me know if there is an alternative.
Regards,
ShilpaHello Shilpa,

> I want to entere data in xml into tables in the database.
> Howevere, I do not want to use OpenXML
> Please let me know if there is an alternative.
A lot depends on what you already have (is it a single file, many files,
is it coming in a stream from pipe, etc) and what you want to do with once
you have in SQL Server (e.g., you going to try to parse the XML into relatio
nal
tables or do you just want to store the data as is in one table?)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Another question is whether you are using SQL Server 2000 or 2005.
I would recommend that you look at the SQLXML components SQLXML Bulkload
functionality...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7419f078c817ae36f52190@.news.microsoft.com...
> Hello Shilpa,
>
> A lot depends on what you already have (is it a single file, many files,
> is it coming in a stream from pipe, etc) and what you want to do with once
> you have in SQL Server (e.g., you going to try to parse the XML into
> relational tables or do you just want to store the data as is in one
> table?)
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Alternative to OpenXML

Hi All,
I want to pass XML and the data in the XML should be stored in the
tables of the database.
However, I do not want to use the OpenXML statement.
Please let me know.
Regards,
Shilpa
Why not use microsoft's XML Parser?
You can download the SDK in the website.
|||Hi Alex,
I am not aware of the XML Parser.
Does it help in entering the data in XML into the tables in the
database?
Could you please give me more insight into this? Also, any sites for
suggested reading.
Regards,
Shilpa

Alternative to OpenXML

Hi,
I want to entere data in xml into tables in the database.
Howevere, I do not want to use OpenXML
Please let me know if there is an alternative.
Regards,
Shilpa
Hello Shilpa,

> I want to entere data in xml into tables in the database.
> Howevere, I do not want to use OpenXML
> Please let me know if there is an alternative.
A lot depends on what you already have (is it a single file, many files,
is it coming in a stream from pipe, etc) and what you want to do with once
you have in SQL Server (e.g., you going to try to parse the XML into relational
tables or do you just want to store the data as is in one table?)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Another question is whether you are using SQL Server 2000 or 2005.
I would recommend that you look at the SQLXML components SQLXML Bulkload
functionality...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7419f078c817ae36f52190@.news.microsoft.co m...
> Hello Shilpa,
>
> A lot depends on what you already have (is it a single file, many files,
> is it coming in a stream from pipe, etc) and what you want to do with once
> you have in SQL Server (e.g., you going to try to parse the XML into
> relational tables or do you just want to store the data as is in one
> table?)
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Alternative to OpenXML

Hi All,
I want to pass XML and the data in the XML should be stored in the
tables of the database.
However, I do not want to use the OpenXML statement.
Please let me know.
Regards,
ShilpaWhy not use microsoft's XML Parser?
You can download the SDK in the website.|||Hi Alex,
I am not aware of the XML Parser.
Does it help in entering the data in XML into the tables in the
database?
Could you please give me more insight into this? Also, any sites for
suggested reading.
Regards,
Shilpa

Alternative to OpenXML

Hi All,
I want to pass XML and the data in the XML should be stored in the
tables of the database. However, I do not want to use the OpenXML
statement. Please let me know.

Regards, ShilpaShilpa (shilpa.nagavara@.unisys.com) writes:
> I want to pass XML and the data in the XML should be stored in the
> tables of the database. However, I do not want to use the OpenXML
> statement. Please let me know.

Which version of SQL Server are you using? If you are on SQL 2000, and
you to insert data in the XML statement piece by piece (and not just
the XML document as such), there is no alternative to OpenXML.

On SQL 2005 you can XQuery to extract data from the XML document.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Actually, there is an alternative for SQL Server 2000 to the OpenXML
statements: SQLXML BulkLoad. It's a COM-based application dll that you
can use to shove XML documents into relational tables. We just started
working with it, and though it's a bit trivky to set up, it actually
performs better with large amounts of XML than the OpenXML procedures.

However, I'm not sure I understand the OP's original question; OpenXML
is used for parsing XML documents into relational tables. If you just
want to store complete XML documents, just store them in a varchar or
text column. Unless you're specifically querying for a value embedded
in the XML document, it's just easier to manage.

HTH,
Stu

Alternative to OpenXML

Hi All,
I want to pass XML and the data in the XML should be stored in the
tables of the database.
However, I do not want to use the OpenXML statement.
Please let me know.
Regards,
ShilpaWhy not use microsoft's XML Parser?
You can download the SDK in the website.|||Hi Alex,
I am not aware of the XML Parser.
Does it help in entering the data in XML into the tables in the
database?
Could you please give me more insight into this? Also, any sites for
suggested reading.
Regards,
Shilpa