sql select data from XML param

Question

whats the SQL for selecting the values from this XML chunk like done in the sample below?

<RWFCriteria reportType="OPRAProject">
   <item id="88" name="" value="" type="Project" />
   <item id="112" name="" value="12" type="Milestone" />
   <item id="43" name="" value="11" type="Milestone" />
</RWFCriteria>

i want to select out similar to this but with the above XML data

DECLARE @Param XML  
SET @Param = '<data>  
      <release id="1"><milestone id="1" /><milestone id="2" /></release>  
      <release id="3"><milestone id="1" /><milestone id="27"/></release>  
    </data>'  

SELECT c.value('../@id', 'INT') AS ReleaseId, c.value('@id', 'INT') AS MilestoneId  
 FROM @Param.nodes('/data/release/milestone') AS T(c)

I want only the data in the nodes where type="Milestone"

marc_s · Accepted Answer · 2010-11-19 18:08:22Z

3

Something like this:

DECLARE @Param XML  
SET @Param = '<RWFCriteria reportType="OPRAProject">
                 <item id="88" name="" value="" type="Project" />
                 <item id="112" name="" value="12" type="Milestone" />
                 <item id="43" name="" value="11" type="Milestone" />
               </RWFCriteria>'  


SELECT 
     RWF.item.value('@id', 'INT') AS 'Id', 
     RWF.item.value('@name', 'VARCHAR(100)') AS 'Name',
     RWF.item.value('@value', 'INT') AS 'Value', 
     RWF.item.value('@type', 'VARCHAR(100)') AS 'Type'
FROM 
     @Param.nodes('/RWFCriteria/item') AS RWF(item)
WHERE
     RWF.item.value('@type', 'VARCHAR(100)') = 'Milestone'

Resulting output:

Id  Name    Value   Type
112           12    Milestone
 43           11    Milestone

edited Nov 19, 2010 at 18:08

answered Nov 19, 2010 at 17:17

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

3 Comments

kacalapy Over a year ago

is there a way i can select only the type="milestone" nodes?

kevev22 Over a year ago

Yes, add to the bottom: WHERE RWF.item.value('@type', 'VARCHAR(100)') = 'Milestone'

marc_s Over a year ago

@kacalapy: sure - updated my answer to select only of type=milestone.

Collectives™ on Stack Overflow

sql select data from XML param

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related