SQL Server Performance

Using XML variable as a table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by SQL_Guess, Sep 11, 2006.

  1. SQL_Guess New Member

    Hi All,<br /><br />Using SQL2005, I'm trying to improve the performance of some code that is currently using: OPENXML, sp_xml_preparedocument and sp_xml_removedocument.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />-----------------------code-----------------------------------<br />set nocount on<br />declare @X xml<br />declare @docHandle int<br />set @X = '&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductInformationEnhancementsDS&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductInformationEnhancements CatalogueItemID="100-0007"&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />riceCheck RetailerName="CompanyA" RetailerPrice="19.9900" RetailerPriceCheckDate="2006-06-17T00:00:00" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />riceCheck RetailerName="CompanyB" RetailerPrice="18.9900" RetailerPriceCheckDate="2006-06-17T00:00:00" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />riceCheck RetailerName="CompanyC" RetailerPrice="20.0000" RetailerPriceCheckDate="2006-06-17T00:00:00" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductSalesRank __Value="1" /&gt;<br /> &lt;/ProductInformationEnhancements&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductInformationEnhancements CatalogueItemID="100-0010"&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />riceCheck RetailerName="CompanyA" RetailerPrice="234.9900" RetailerPriceCheckDate="2006-06-17T00:00:00" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />riceCheck RetailerName="CompanyB" RetailerPrice="240.0000" RetailerPriceCheckDate="2006-06-17T00:00:00" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />riceCheck RetailerName="CompanyC" RetailerPrice="231.9900" RetailerPriceCheckDate="2006-06-17T00:00:00" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductSalesRank __Value="2" /&gt;<br /> &lt;/ProductInformationEnhancements&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductInformationEnhancements CatalogueItemID="100-0019"&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />riceCheck RetailerName="CompanyA" RetailerPrice="1250.0000" RetailerPriceCheckDate="2006-06-17T00:00:00" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />riceCheck RetailerName="CompanyB" RetailerPrice="1200.0000" RetailerPriceCheckDate="2006-06-17T00:00:00" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductSalesRank __Value="4" /&gt;<br /> &lt;/ProductInformationEnhancements&gt;<br />&lt;/ProductInformationEnhancementsDS&gt;'<br /><br />EXEC sp_xml_preparedocument @docHandle OUTPUT, @X<br />select PIE.CatalogueItemID as SalesRank<br />FROM<br />OPENXML(@docHandle,'/ProductInformationEnhancementsDS/ProductInformationEnhancements',1)<br /> WITH (CatalogueItemID CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />) as PIE<br />exec sp_xml_removedocument @docHandle<br /><br />select<br /> Pie.CatId.value('(//@CatalogueItemID)[1]','CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />') as CatalogItemID<br />--, Pie.CatId.value('(//@__Value)[1]','INT') as SalesRank<br />from @x.nodes('/ProductInformationEnhancementsDS/ProductInformationEnhancements') as PIE(CatId)<br />/*<br />select Pie.CatId.query('data(//@CatalogueItemID)'), Pie.CatId.query('data(//@CatalogueItemID)')<br />from @x.nodes('/ProductInformationEnhancementsDS/ProductInformationEnhancements') as PIE(CatId)<br />*/<br />-----------------------code-----------------------------------<br /></font id="code"></pre id="code"><br />The OPENXML returns (correctly):<br />SalesRank<br />---------<br />100-0007<br />100-0010<br />100-0019<br />Whereas the .Query returns :<br />CatalogItemID<br />-------------<br />100-0007<br />100-0007<br />100-0007<br /><br />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:<br />Msg 2389, Level 16, State 1, Line 32<br />XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'<br /><br />Ideas?<br /><br />Some progress:<br /><pre id="code"><font face="courier" size="2" id="code"><br />select<br /> CatId.value('(@CatalogueItemID)[1]','CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />')<br /> as CatalogItemID<br />, CatId.value('(//@__Value)[1]','INT') as SalesRank<br />from @x.nodes('/ProductInformationEnhancementsDS/ProductInformationEnhancements') as PIE(CatId)<br /></font id="code"></pre id="code"><br />Returns:<br /><pre id="code"><font face="courier" size="2" id="code"><br />CatalogItemID SalesRank<br />------------- -----------<br />100-0007 1<br />100-0010 1<br />100-0019 1<br /></font id="code"></pre id="code"><br />... so I'm now correctly getting the CatalogItemID, but now I need to also get the associated SalesRank for that node...<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown<br /><br /><br />... some more progress:<br /><br />some more progress:<br />select<br /> CatId.value('(@CatalogueItemID)[1]','CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />')<br /> as CatalogItemID<br />, CatId.query('.//ProductSalesRank')--(data//ProductSalesRank/__Value)') as SalesRank<br />from @x.nodes('/ProductInformationEnhancementsDS/ProductInformationEnhancements') as PIE(CatId)<br /><br />returns:<br /><br />CatalogItemID<br />------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />100-0007 &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductSalesRank __Value="1" /&gt;<br />100-0010 &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductSalesRank __Value="2" /&gt;<br />100-0019 &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />roductSalesRank __Value="4" /&gt;<br /><br />Am I getting lost, or closer?<br />
  2. SQL_Guess New Member

    solution:<br /><br /><br /><br />select Pie.CatId.value('@CatalogueItemID','CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />') as CatalogItemID,<br />Pie.CatId.value('ProductSalesRank[1]/@__Value', 'INT') as [Sales Rank]<br />FROM<br />@x.nodes('/ProductInformationEnhancementsDS/ProductInformationEnhancements') as PIE(CatId)<br /><br />Now I just need to try and understand what I was doing wrong when I tried something like thais, that caused the value error message I mentioned above<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

Share This Page