SQL Server 2005 XML Query

Question

I'm new to querying XML datatype in SQL Server 2005. Anyone can help me create a query for my requirement? Here's a scenario of my column.

Column Name: Cabinet

/*Row 1 XML Data*/
<shelf>
 <box>
   <item type="pencil" color="blue"/>
   <item type="pencil" color="red"/>
   <item type="paper" color="white"/>
   <item type="ribbon" color="red"/>
 </box>
<shelf>

/*Row 2 XML Data*/
<shelf>   
  <item type="pencil" color="yellow"/>
  <item type="can" color="blue"/>
  <item type="scissor" color="yellow"/>
<shelf>

Desired Output:
4
3

I want to count the number of "item" nodes regardless of its type & color. Thanks in advance.

Adriaan Stander · Accepted Answer · 2010-03-08 14:50:45Z

2

Have a look at this (Full working example)

DECLARE @Table TABLE(
        Cabinet XML
)

INSERT INTO @Table SELECT
'<shelf> 
 <box> 
   <item type="pencil" color="blue"/> 
   <item type="pencil" color="red"/> 
   <item type="paper" color="white"/> 
   <item type="ribbon" color="red"/> 
 </box> 
</shelf>'

INSERT INTO @Table SELECT
'<shelf>    
  <item type="pencil" color="yellow"/> 
  <item type="can" color="blue"/> 
  <item type="scissor" color="yellow"/> 
</shelf>'

SELECT  *,
        Cabinet.query('count(//item)').value('.','int')
FROM    @Table

answered Mar 8, 2010 at 14:50

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server 2005 XML Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related