SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Retrive data from XML node group by att value		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sadiqmodan
Starting Member

India
16 Posts
Posted - 11/16/2012 :  07:02:55  Show Profile  Reply with Quote
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
Yak Posting Veteran

Romania
95 Posts
Posted - 11/16/2012 :  08:08:56  Show Profile  Reply with Quote
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, '') ,'')
Edited by - stepson on 11/16/2012 08:16:01
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/16/2012 :  08:11:36  Show Profile  Reply with Quote
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)
Go to Top of Page

sadiqmodan
Starting Member

India
16 Posts
Posted - 11/19/2012 :  00:28:22  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Yak Posting Veteran

Romania
95 Posts
Posted - 11/19/2012 :  01:58:36  Show Profile  Reply with Quote
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, '') ,'')
Edited by - stepson on 11/19/2012 01:59:24
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48101 Posts
Posted - 11/19/2012 :  02:28:27  Show Profile  Reply with Quote
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/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48101 Posts
Posted - 11/19/2012 :  02:35:41  Show Profile  Reply with Quote
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/
Go to Top of Page

sadiqmodan
Starting Member

India
16 Posts
Posted - 11/19/2012 :  03:46:56  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48101 Posts
Posted - 11/20/2012 :  03:01:20  Show Profile  Reply with Quote
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/
Go to Top of Page

stepson
Yak Posting Veteran

Romania
95 Posts
Posted - 11/20/2012 :  04:00:05  Show Profile  Reply with Quote
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
Edited by - stepson on 11/20/2012 04:00:37
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000