XML Query Help Required.. - SQL Server Forums

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)

XML Query Help Required..

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-11-30 : 00:57:29

I have xml column in which values stored like


<filter id="1">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="2">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>

I want to fetch values using single query with comma seperation.. like KSE,LSA
I written query but not able to get it..


select 
   xmlcolumn.query(' 
		<s>{ for $k in /STATUS2/attrib/@value
				return string($k)
		}</s>'),
* from xmltable

Above query does not return with comma seperated values and it also requires node <s>.

I need values comma seperated..like KSE,LSA

Each xml stored row wise and multiple rows for each filter(s)

and returning result will return two rows for each record xml if two filter exists in xml
Please help

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-11-30 : 03:42:37

Please help some (1)

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-12-01 : 01:46:59

Please help ..
i tried but not able to get it sol.

Sachin.Nand

2937 Posts

Posted - 2010-12-01 : 07:16:36

[code]
declare @x xml='<filter id="1">
<STATUS2 Operator="LIST">
<attrib value="KSE" DataType="string" />
<attrib value="LSA" DataType="string" />
</STATUS2>
<GRD Operator="LIST">
<attrib value="K" DataType="string" />
<attrib value="S33" DataType="string" />
</GRD>
<QUANTITY Operator="EQUAL" DataType="integer" value="20" />
</filter>
<filter id="2">
<STATUS2 Operator="LIST">
<attrib value="SSSE" DataType="string" />
<attrib value="DEA" DataType="string" />
</STATUS2>
<GRD Operator="LIST">
<attrib value="K" DataType="string" />
<attrib value="B" DataType="string" />
</GRD>
<SPD>
<attrib value="33" DataType="string" />
<attrib value="323" DataType="string" />
</SPD>
<QUANTITY Operator="EQUAL" DataType="integer" value="10" />
</filter>'

declare @value varchar(30)=''
;with cte
as
(
select
x.i.value('../../@id','varchar(40)')id,
x.i.value('@value','varchar(40)') value
from @x.nodes('filter/STATUS2/attrib')x(i)
)

select id,stuff((select ','+ value from cte c1 where c1.id=c2.id for XML path('')),1,1,'')value from cte c2
group by c2.id
[/code]

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-12-01 : 08:56:40

I have one xml stored in each column of table and i want to use that column of table then how can use in place of @x.nodes to tablename.column name ??

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-01 : 09:54:13

basically same thing as Sachin showed


declare @x XML

CREATE TABLE #test(myxml XML)

INSERT INTO #test
        ( myxml )
VALUES  ('<filter id="1">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="2">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>')

SELECT @x = (SELECT myxml FROM #test)


--declare @value varchar(30)=''


;with cte
as
(
select 
x.i.value('../../@id','varchar(40)')id,
x.i.value('@value','varchar(40)') value
from @x.nodes('filter/STATUS2/attrib')x(i)
)

select id,stuff((select  ','+ value  from cte c1 where c1.id=c2.id for XML path('')),1,1,'')value from cte c2
group by c2.id



DROP TABLE #test

If you don't have the passion to help people, you have no passion

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-12-01 : 09:59:39

I HAVE multiple rows with xml in one table..
Please try to insert more that one rows in table and diff. filters..
and want to execute it in one shot..

quote:

Originally posted by yosiasz

basically same thing as Sachin showed


declare @x XML

CREATE TABLE #test(myxml XML)

INSERT INTO #test
        ( myxml )
VALUES  ('<filter id="1">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="2">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>')

SELECT @x = (SELECT myxml FROM #test)


--declare @value varchar(30)=''


;with cte
as
(
select 
x.i.value('../../@id','varchar(40)')id,
x.i.value('@value','varchar(40)') value
from @x.nodes('filter/STATUS2/attrib')x(i)
)

select id,stuff((select  ','+ value  from cte c1 where c1.id=c2.id for XML path('')),1,1,'')value from cte c2
group by c2.id



DROP TABLE #test

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-01 : 10:48:54

try this


declare @x XML

CREATE TABLE #test(xml1 XML, xml2 xml)

INSERT INTO #test
        ( xml1, xml2 )
VALUES  ('<filter id="1">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="2">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>',
'<filter id="1">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="2">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>')


	--SELECT a.value('@value[1]', 'nvarchar(50)') AS [name]
	--FROM #test
	--CROSS APPLY xml1.nodes('filter/STATUS2/attrib') AS X(a)
	----CROSS APPLY xml2.nodes('filter/STATUS2/attrib') AS Y(b)
	
;with cte
as
(

	SELECT a.value('../../@id','varchar(40)')id,
	       a.value('@value[1]', 'varchar(40)') AS value
	FROM #test
	CROSS APPLY xml1.nodes('filter/STATUS2/attrib') AS X(a)
)

select id,stuff((select  ','+ value  
                   from cte c1 
                  where c1.id=c2.id 
                     for XML path('')),1,1,'')value 
  from cte c2
 group by c2.id

If you don't have the passion to help people, you have no passion

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-01 : 11:09:00

hi,
This is another approach

declare @s xml=
'<filter id="1">
<STATUS2 Operator="LIST">
<attrib value="KSE" DataType="string" />
<attrib value="LSA" DataType="string" />
</STATUS2>
<GRD Operator="LIST">
<attrib value="K" DataType="string" />
<attrib value="S33" DataType="string" />
</GRD>
<QUANTITY Operator="EQUAL" DataType="integer" value="20" />
</filter>
<filter id="2">
<STATUS2 Operator="LIST">
<attrib value="SSSE" DataType="string" />
<attrib value="DEA" DataType="string" />
</STATUS2>
<GRD Operator="LIST">
<attrib value="K" DataType="string" />
<attrib value="B" DataType="string" />
</GRD>
<SPD>
<attrib value="33" DataType="string" />
<attrib value="323" DataType="string" />
</SPD>
<QUANTITY Operator="EQUAL" DataType="integer" value="10" />
</filter>'

select stuff(
(select t.value+','
from
(
select [value]= t.s.value('@value','varchar(100)')
from
@s.nodes('//attrib') t(s)
)t
for xml path('')),1,1,'')

Iam a slow walker but i never walk back

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-12-01 : 12:19:25

Thank you.. but if i enter multiple rows in xml1 column.. it will return all values comma seperated..
e.g if i insert same row in xml1 column then it returns output as


1	KSE,LSA,KSE,LSA
2	SSSE,DEA,SSSE,DEA

It should return like each row...
1     KSE, LSA  [filter 1's values]
1     SSSE, DEA [filter 1's values]
2     KSE, LSA  [filter 2's values]
2     SSSE, DEA [filter 2's values]

quote:

Originally posted by yosiasz

try this


declare @x XML

CREATE TABLE #test(xml1 XML, xml2 xml)

INSERT INTO #test
        ( xml1, xml2 )
VALUES  ('<filter id="1">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="2">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>',
'<filter id="1">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="2">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>')


	--SELECT a.value('@value[1]', 'nvarchar(50)') AS [name]
	--FROM #test
	--CROSS APPLY xml1.nodes('filter/STATUS2/attrib') AS X(a)
	----CROSS APPLY xml2.nodes('filter/STATUS2/attrib') AS Y(b)
	
;with cte
as
(

	SELECT a.value('../../@id','varchar(40)')id,
	       a.value('@value[1]', 'varchar(40)') AS value
	FROM #test
	CROSS APPLY xml1.nodes('filter/STATUS2/attrib') AS X(a)
)

select id,stuff((select  ','+ value  
                   from cte c1 
                  where c1.id=c2.id 
                     for XML path('')),1,1,'')value 
  from cte c2
 group by c2.id

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-01 : 12:35:14

that was just sample data. Don't just copy paste try to understand what has been posted first


declare @x XML

CREATE TABLE #test(xml1 XML, xml2 xml)

INSERT INTO #test
        ( xml1, xml2 )
VALUES  ('<filter id="1">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="2">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>',
'<filter id="1">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="2">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>')


INSERT INTO #test
        ( xml1, xml2 )
VALUES  ('<filter id="3">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="4">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>',
'<filter id="5">
    <STATUS2 Operator="LIST">
      <attrib value="KSE" DataType="string" />
      <attrib value="LSA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="S33" DataType="string" />
    </GRD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="20" />
</filter>
<filter id="6">
    <STATUS2 Operator="LIST">
      <attrib value="SSSE" DataType="string" />
      <attrib value="DEA" DataType="string" />
    </STATUS2>
    <GRD Operator="LIST">
      <attrib value="K" DataType="string" />
      <attrib value="B" DataType="string" />
    </GRD>
    <SPD>
      <attrib value="33" DataType="string" />
      <attrib value="323" DataType="string" />
    </SPD>
    <QUANTITY  Operator="EQUAL"  DataType="integer" value="10" />
</filter>')


	--SELECT a.value('@value[1]', 'nvarchar(50)') AS [name]
	--FROM #test
	--CROSS APPLY xml1.nodes('filter/STATUS2/attrib') AS X(a)
	----CROSS APPLY xml2.nodes('filter/STATUS2/attrib') AS Y(b)
	
;with cte
as
(

	SELECT a.value('../../@id','varchar(40)') id,
	       a.value('@value[1]', 'varchar(40)') AS value
	FROM #test
	CROSS APPLY xml1.nodes('filter/STATUS2/attrib') AS X(a)
	--GROUP BY a.value('../../@id','varchar(40)')
)

select id,stuff((select  ','+ value  
                   from cte c1 
                  where c1.id=c2.id 
                     for XML path('')),1,1,'')value 
  from cte c2
 group by c2.id


DROP TABLE #test

If you don't have the passion to help people, you have no passion

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-12-01 : 12:54:47

yes.. i know it.. but
i have filter/criteria stored in xml in one table
and that xml stored in each row for that table..

i have to filter from table from that xml stored..
and in xml.. there is multiple filters are stored..

so it will return multiple row with comma seperated value.. as i mentioned..

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-01 : 14:20:10

je ne comprends pas ce que tu dis

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources