1

I have a database table named Warehouses that contains rows of data related to specific warehouses. One column in that table is named itemStats and contains XML data in the following format:

<itemStats xmlns="http://www.blahblahblah.com/blah">
  <itemCounts>
    <item>
      <name>Toaster Oven Device</name>
      <count>6</count>
    </item>
    <item>
      <name>Washing Machine</name>
      <count>2</count>
    </item>
    <item>
      <name>Microwave Oven</name>
      <count>4</count>
    </item>
  </itemCounts>

I'd like to query the table to check that column in each row for the existence of some string (example: Oven) and if it finds it, return the count associated with it. So if I had the above data in itemStats for a given row in Warehouses, I'd like it to return the following along with other matches from other rows:

Name                Count
=========================
Toaster Oven Device     6
Microwave Oven          4

I've attempted several times using SQL value() and nodes() but am having trouble producing the desired results. I know the [1] is wrong in the below query, but I'm not sure how to reference a dynamic location:

;WITH XMLNAMESPACES (N'http://www.blahblahblah.com/blah' as X)
SELECT itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:name)[1]', 'nvarchar(max)') as Name,
       itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:count)[1]', 'int') as Count
FROM Warehouses
WHERE itemStats.exist('(/X:itemStats/X:itemCounts/X:item/X:name[contains(., "Oven")])') = 1
0

Use nodes() and value() to get the names and count in a derived table and filter your rows in the main query.

declare @T table(itemStates xml not null);

insert into @T (itemStates)
values ('<itemStats xmlns="http://www.blahblahblah.com/blah">
           <itemCounts>
             <item>
               <name>Toaster Oven Device</name>
               <count>6</count>
             </item>
             <item>
               <name>Washing Machine</name>
               <count>2</count>
             </item>
             <item>
               <name>Microwave Oven</name>
               <count>4</count>
             </item>
           </itemCounts>
         </itemStats>');

with xmlnamespaces(default 'http://www.blahblahblah.com/blah')
select T.Name,
       T.Count
from (
     select I.X.value('(name/text())[1]', 'nvarchar(max)') as Name,
            I.X.value('(count/text())[1]', 'int') as Count
     from @T as T
       cross apply T.itemStates.nodes('/itemStats/itemCounts/item') as I(X)
     ) as T
where T.Name like '%Oven%';
  • Thank you for providing this and the brief explanation. It did help me better understand the path to the solution. – Lava Viperidae Oct 11 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.