XML value frm few nodes based on another node value

Question

I cannot find solution to my problem, I hope it will be easy to somebody.

Below is part of my xml:

<Income>    
    <IncomeItem>
         <TypeId>29</TypeId>
         <Description>test1</Description>
     </IncomeItem>
     <IncomeItem>
         <TypeId>29</TypeId>
         <Description>test2</Description>
     </IncomeItem>
     <IncomeItem>
        <TypeId>29</TypeId>
        <Description>test3</Description>
    </IncomeItem>
</Income>

I need output like: test1 test2 test3

There will be more types of TypeId so value of Description has to be based on TypeId.

Pseudo code would look like :

Select Decsription From XML where TypeId = 29

Please don't just ask us to solve the problem for you. Show us how you tried to solve the problem yourself, then show us exactly what the result was, and tell us why you feel it didn't work. See "What Have You Tried?" for an excellent article that you really need to read. — John Saunders
– John Saunders, Commented Jun 2, 2015 at 14:26

har07 · Accepted Answer · 2015-06-02 14:18:58Z

1

This is one possible way :

declare @xml XML = '<Income>    
    <IncomeItem>
         <TypeId>29</TypeId>
         <Description>test1</Description>
     </IncomeItem>
     <IncomeItem>
         <TypeId>29</TypeId>
         <Description>test2</Description>
     </IncomeItem>
     <IncomeItem>
        <TypeId>29</TypeId>
        <Description>test3</Description>
    </IncomeItem>
</Income>'

SELECT x.value('Description[1]','varchar(max)') as 'description'
FROM @xml.nodes('//IncomeItem[TypeId=29]') i(x)

If you want Description values to be concatenated as one row, one possible way is using xquery for loop (and concat() function to append space between values, if needed) :

SELECT @xml.query('for $d in //IncomeItem[TypeId=29]/Description return concat($d, " ")')
           .value('.','varchar(max)') as 'description'

SQL Fiddle

For reference : [MSDN] FLWOR Statement and Iteration (XQuery)

edited Jun 2, 2015 at 14:18

answered Jun 2, 2015 at 13:55

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

2 Comments

Pawel Czapski Over a year ago

Thank you @har07. But this returns more than one value. I need to have this as one value to set other value equal this value. Any suggestions?

har07 Over a year ago

@richardpawel you can use xquery loop to concatenate all relevant Description into one value, check my update for detailed query

Collectives™ on Stack Overflow

XML value frm few nodes based on another node value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related