SQL Server Performance Forum – Threads Archive

Using XML variable as a table

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. <pre id="code"> ———————–code———————————– set nocount on declare @X xml declare @docHandle int set @X = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductInformationEnhancementsDS> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductInformationEnhancements CatalogueItemID="100-0007"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />riceCheck RetailerName="CompanyA" RetailerPrice="19.9900" RetailerPriceCheckDate="2006-06-17T00:00:00" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />riceCheck RetailerName="CompanyB" RetailerPrice="18.9900" RetailerPriceCheckDate="2006-06-17T00:00:00" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />riceCheck RetailerName="CompanyC" RetailerPrice="20.0000" RetailerPriceCheckDate="2006-06-17T00:00:00" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductSalesRank __Value="1" /> </ProductInformationEnhancements> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductInformationEnhancements CatalogueItemID="100-0010"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />riceCheck RetailerName="CompanyA" RetailerPrice="234.9900" RetailerPriceCheckDate="2006-06-17T00:00:00" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />riceCheck RetailerName="CompanyB" RetailerPrice="240.0000" RetailerPriceCheckDate="2006-06-17T00:00:00" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />riceCheck RetailerName="CompanyC" RetailerPrice="231.9900" RetailerPriceCheckDate="2006-06-17T00:00:00" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductSalesRank __Value="2" /> </ProductInformationEnhancements> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductInformationEnhancements CatalogueItemID="100-0019"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />riceCheck RetailerName="CompanyA" RetailerPrice="1250.0000" RetailerPriceCheckDate="2006-06-17T00:00:00" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />riceCheck RetailerName="CompanyB" RetailerPrice="1200.0000" RetailerPriceCheckDate="2006-06-17T00:00:00" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductSalesRank __Value="4" /> </ProductInformationEnhancements> </ProductInformationEnhancementsDS>' EXEC sp_xml_preparedocument @docHandle OUTPUT, @X select PIE.CatalogueItemID as SalesRank FROM OPENXML(@docHandle,’/ProductInformationEnhancementsDS/ProductInformationEnhancements’,1) WITH (CatalogueItemID CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) as PIE exec sp_xml_removedocument @docHandle select Pie.CatId.value(‘(//@CatalogueItemID)[1]’,’CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’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———————————– </pre id="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: <pre id="code"> select CatId.value(‘(@CatalogueItemID)[1]’,’CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />’) as CatalogItemID , CatId.value(‘(//@__Value)[1]’,’INT’) as SalesRank from @x.nodes(‘/ProductInformationEnhancementsDS/ProductInformationEnhancements’) as PIE(CatId) </pre id="code"> Returns: <pre id="code"> CatalogItemID SalesRank ————- ———– 100-0007 1 100-0010 1 100-0019 1 </pre id="code"> … so I’m now correctly getting the CatalogItemID, but now I need to also get the associated SalesRank for that node… Panic, Chaos, Disorder … my work here is done –unknown … some more progress: some more progress: select CatId.value(‘(@CatalogueItemID)[1]’,’CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />’) as CatalogItemID , CatId.query(‘.//ProductSalesRank’)–(data//ProductSalesRank/__Value)’) as SalesRank from @x.nodes(‘/ProductInformationEnhancementsDS/ProductInformationEnhancements’) as PIE(CatId) returns: CatalogItemID ————- ———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————- ———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————- 100-0007 &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductSalesRank __Value="1" /> 100-0010 &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductSalesRank __Value="2" /> 100-0019 &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roductSalesRank __Value="4" /> Am I getting lost, or closer?

solution: select Pie.CatId.value(‘@CatalogueItemID’,’CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />’) as CatalogItemID, Pie.CatId.value(‘ProductSalesRank[1]/@__Value’, ‘INT’) as [Sales Rank] FROM @x.nodes(‘/ProductInformationEnhancementsDS/ProductInformationEnhancements’) as PIE(CatId) 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 Panic, Chaos, Disorder … my work here is done –unknown

]]>