Retrive data from XML node grouping with attribute value in Xquery

Question

I have XML Data like

<Answers>
<AnswerSet>
    <Answer questionId="Name" IncludeInGroup="false">ds</Answer>
    <Answer questionId="Email" IncludeInGroup="false">sds</Answer>
    <Answer questionId="Password" IncludeInGroup="false">QqH4x4jhNCs=</Answer>
    <Answer questionId="GUID" IncludeInGroup="false">dsdsd</Answer>
    <Answer questionId="RegistrationIDInfo" IncludeInGroup="false">sdsd</Answer>
    <Answer questionId="Login" type="list" IncludeInGroup="false" value="1" color="blue">Enabled</Answer>
    <Answer questionId="FriendList" IncludeInGroup="false" RegionName="Friends" RepeatRegion="0">ds</Answer>   
    <Answer questionId="testDropdown" type="list" selectedValue="2" IncludeInGroup="false" TP="1" TP1="2">item2</Answer>
    <Answer questionId="testRadio" type="list" IncludeInGroup="false" value="3" color="222" test="123" a="1" a1="22">item234</Answer>
    <Answer questionId="TestCheckbox" type="list" value="Item1" RepeatRegion="0" IncludeInGroup="false">Item1</Answer>
    <Answer questionId="TestCheckbox" type="list" value="Item3" RepeatRegion="0" IncludeInGroup="false">Item3</Answer>
    <Answer questionId="TestCheckbox" type="list" value="Item1" RepeatRegion="1" IncludeInGroup="false">Item1</Answer>
    <Answer questionId="TestCheckbox" type="list" value="Item2" RepeatRegion="1" IncludeInGroup="false">Item2</Answer>
</AnswerSet>

Need to get data of questionid="TestCheckbox" with group by repeatregion attribute

Output

Item1,Item3|Item1,Item2

I have tried with below query but it's returning Item1|Item3|Item1|Item2

declare @Content as XML set @Content='Above XML...' declare @Field as varchar(100) set @Field='TestCheckbox' SELECT ISNULL( STUFF( (select '|' + T.answers.value('.', 'VARCHAR(max)') FROM @Content.nodes('Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]') AS T(answers) for xml path('')), 1, 1, '') ,'')

Please guide me to get above output.

Thanks.

score 1 · Answer 1 · answered Nov 16 '12 at 12:24

Try this

;with cte as
(
select 
t.Answers.value('@RepeatRegion', 'int') region,
T.answers.value('.', 'VARCHAR(max)') value
FROM @Content.nodes('Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]') AS T(answers) 
) 

select
    STUFF ((
        select '|' + v.results
        from
        (       
        SELECT distinct 
        top 100 percent
            region,
            stuff(
            (
                select cast(',' as varchar(max)) + c1.value
            from cte c1
            WHERE c1.region = cte.region
            for xml path('')
            ), 1, 1, '') AS results
        FROM
            cte
        order by cte.region

        ) v
        for xml path('')
    ),1,1,'')

Thanks for quick response podiluska, if we have to get data of Value (value="Item1") attribute instead of innertext. and attribute name passed using Sql variable like.. Declare @Att as Varchar(50) Set @Att='Value'... Any Idea? — Mohmedsadiq, Nov 16 '12 at 13:18
Anyone have any idea to get attribute value using SQl variable from XML ? — Mohmedsadiq, Nov 18 '12 at 02:45

Retrive data from XML node grouping with attribute value in Xquery

Output

1 Answers1