September 11, 2006 at 7:16 am
Hi All,
Using SQL2005, I'm trying to improve the performance of some code that is currently using: OPENXML, sp_xml_preparedocument and sp_xml_removedocument.
-----------------------code-----------------------------------
set nocount on
declare @X xml
declare @docHandle int
set @X = '
EXEC sp_xml_preparedocument @docHandle OUTPUT, @X
select PIE.CatalogueItemID as SalesRank
FROM
OPENXML(@docHandle,'/ProductInformationEnhancementsDS/ProductInformationEnhancements',1)
WITH (CatalogueItemID CHAR(8)) as PIE
exec sp_xml_removedocument @docHandle
select
Pie.CatId.value('(//@CatalogueItemID)[1]','CHAR(8)') as CatalogItemID
--, Pie.CatId.value('(//@__Value)[1]','INT') as SalesRank
from @x.nodes('/ProductInformationEnhancementsDS/ProductInformationEnhancements') as PIE(CatId)
/*
select Pie.CatId.query('data(//@CatalogueItemID)'), Pie.CatId.query('data(//@CatalogueItemID)')
from @x.nodes('/ProductInformationEnhancementsDS/ProductInformationEnhancements') as PIE(CatId)
*/
-----------------------code-----------------------------------
The OPENXML returns (correctly):
SalesRank
---------
100-0007
100-0010
100-0019
Whereas the .Query returns :
CatalogItemID
-------------
100-0007
100-0007
100-0007
Now, I'm trying to find out if I'm going down the right road here. It SEEMS like the singleton in the .VALUE might be the problem, but if I try to cahnge that I get:
Msg 2389, Level 16, State 1, Line 32
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
Ideas?
Some progress:
select
CatId.value('(@CatalogueItemID)[1]','CHAR(8)')
as CatalogItemID
, CatId.value('(//@__Value)[1]','INT') as SalesRank
from @x.nodes('/ProductInformationEnhancementsDS/ProductInformationEnhancements') as PIE(CatId)
Returns:
CatalogItemID SalesRank
------------- -----------
100-0007 1
100-0010 1
100-0019 1
... so I'm now correctly getting the CatalogItemID, but now I need to also get the associated SalesRank for that node...
September 11, 2006 at 8:45 am
solution:
select Pie.CatId.value('@CatalogueItemID','CHAR(8)') as CatalogItemID,
Pie.CatId.value('ProductSalesRank[1]/@__Value', 'INT') as [Sales Rank]
FROM
@x.nodes('/ProductInformationEnhancementsDS/ProductInformationEnhancements') as PIE(CatId)
September 12, 2006 at 8:48 am
Well, you seemed to have found your own answer here -- and you definitely know more about OpenXML than I do <g> --
But, FWIW and for future reference for anybody else reading this thread with interest, it is almost *never* correct to put "//" in an XPATH expression in production code, it's the performance equivalent of SELECT * only worse. You're basically making the engine look at all levels of the tree for something that matches your expression. This might be okay in some simple SQL-based XML, where the tree is relatively flat and regular. But even in SQL-based XML, if you have nodes representing different tables you should be able to drill down explicitly in the one you're looking for. The expression with // might not find you any erroneous nodes if the other tables don't have the same column, but think of the waste while the engine looks for them!
When the XML represents a document, with potentially deeply-nested and irregular contents... well... as River said in "Serenity", "Things are going to get much, much worse." Let's just leave it at that <g>.
You can use the "//" while you're doing some early tests and figuring things out, just like you might use SELECT * from the Query Analyzer. Then fix it.
>L<
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply