Retrive data from XML node group by att value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrive data from XML node group by att value

Author

Topic

sadiqmodan
Starting Member

16 Posts

Posted - 2012-11-16 : 07:02:55

Hello,

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>
</Answers>

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

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, '') ,'')

Output Required
Item1,Item3|Item1,Item2

Please guide me to get above output.

Thanks.

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-16 : 08:08:56

you base your split base on attribute RepeatRegion , I guess

SELECT ISNULL( STUFF( (

select case when B.y=coalesce(A.y,0) then ',' else '|' end + B.x
from
(
select t.c.value('@value[1]','varchar(30)') as x,
t.c.value('@RepeatRegion[1]','varchar(30)') as y,
row_number() OVER ( order by t.c.value('@RepeatRegion[1]','varchar(30)') ) as rowIndex
from @Content.nodes('/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]') as t(c))A

full join
(select t.c.value('@value[1]','varchar(30)') as x,
t.c.value('@RepeatRegion[1]','varchar(30)') as y,
row_number() OVER ( order by t.c.value('@RepeatRegion[1]','varchar(30)') ) as rowIndex
from @Content.nodes('/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]') as t(c))B on A.rowIndex=B.rowIndex-1

where B.X is not null
for xml path('') ), 1, 1, '') ,'')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 08:11:36

Query it like shown below and then pivot the results, or use XML path like you were trying to do, if you need to stuff it into comma-separated strings

DECLARE @Content XML = '<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>
</Answers>';

SELECT
	c2.value('@value','varchar(32)') AS ValueCol,
	c2.value('@RepeatRegion','int') AS RepeatRegion
FROM
	@Content.nodes('/Answers/AnswerSet') T1(c1)
	CROSS APPLY c1.nodes('./Answer[@questionId="TestCheckbox"]') T2(c2)

sadiqmodan
Starting Member

16 Posts

Posted - 2012-11-19 : 00:28:22

Hi Stepson and sunitabeck,

Thanks for quick reply, @Stepson, using your solution I can get the exact result, can we pass @Value attribute in Sql variable ?

I have tried to modify your query with Sql variable but did not get proper solution to get @value attribute data using Sql variable.

Thanks,
Mohmedsadiq

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-19 : 01:58:36

declare @attr as varchar(100)
set @attr='value'

SELECT ISNULL( STUFF( (

select case when B.y=coalesce(A.y,0) then ',' else '|' end + B.x
from
(
select t.c.value('(@*[local-name() = sql:variable("@attr")])[1]','varchar(30)') as x,
t.c.value('@RepeatRegion[1]','varchar(30)') as y,
row_number() OVER ( order by t.c.value('@RepeatRegion[1]','varchar(30)') ) as rowIndex
from @Content.nodes('/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]') as t(c))A

full join
(select t.c.value('(@*[local-name() = sql:variable("@attr")])[1]','varchar(30)') as x,
t.c.value('@RepeatRegion[1]','varchar(30)') as y,
row_number() OVER ( order by t.c.value('@RepeatRegion[1]','varchar(30)') ) as rowIndex
from @Content.nodes('/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]') as t(c))B on A.rowIndex=B.rowIndex-1

where B.X is not null
for xml path('') ), 1, 1, '') ,'')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-19 : 02:28:27

quote:
Originally posted by sadiqmodan

Hi Stepson and sunitabeck,

Thanks for quick reply, @Stepson, using your solution I can get the exact result, can we pass @Value attribute in Sql variable ?

I have tried to modify your query with Sql variable but did not get proper solution to get @value attribute data using Sql variable.

Thanks,
Mohmedsadiq

do you mean attributename itself comes from sql variable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-19 : 02:35:41

see this to understand how you can get attributename from attributes to check against variables

http://visakhm.blogspot.in/2012/10/shred-data-as-well-as-metadata-from-xml.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sadiqmodan
Starting Member

16 Posts

Posted - 2012-11-19 : 03:46:56

quote:
Originally posted by visakh16

quote:
Originally posted by sadiqmodan

Hi Stepson and sunitabeck,

Thanks for quick reply, @Stepson, using your solution I can get the exact result, can we pass @Value attribute in Sql variable ?

I have tried to modify your query with Sql variable but did not get proper solution to get @value attribute data using Sql variable.

Thanks,
Mohmedsadiq

do you mean attributename itself comes from sql variable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, we pass attribute name in SQL variable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-20 : 03:01:20

quote:
Originally posted by sadiqmodan

quote:
Originally posted by visakh16

quote:
Originally posted by sadiqmodan

Hi Stepson and sunitabeck,

Thanks for quick reply, @Stepson, using your solution I can get the exact result, can we pass @Value attribute in Sql variable ?

I have tried to modify your query with Sql variable but did not get proper solution to get @value attribute data using Sql variable.

Thanks,
Mohmedsadiq

do you mean attributename itself comes from sql variable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, we pass attribute name in SQL variable.

see the link to retrieve attribute names from within a node

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-20 : 04:00:05

did you try my second post?

declare @attr as varchar(100)
set @attr='value'

SELECT ISNULL( STUFF( (

select case when B.y=coalesce(A.y,0) then ',' else '|' end + B.x
from
(
select t.c.value('(@*[local-name() = sql:variable("@attr")])[1]','varchar(30)') as x,
t.c.value('@RepeatRegion[1]','varchar(30)') as y,
row_number() OVER ( order by t.c.value('@RepeatRegion[1]','varchar(30)') ) as rowIndex
from @Content.nodes('/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]') as t(c))A

full join
(select t.c.value('(@*[local-name() = sql:variable("@attr")])[1]','varchar(30)') as x,
t.c.value('@RepeatRegion[1]','varchar(30)') as y,
row_number() OVER ( order by t.c.value('@RepeatRegion[1]','varchar(30)') ) as rowIndex
from @Content.nodes('/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]') as t(c))B on A.rowIndex=B.rowIndex-1

where B.X is not null
for xml path('') ), 1, 1, '') ,'')

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut

sabinWeb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources