Grouping results in SQL Server 2005 using xquery

Question

I am trying to extract data, to use in a rss feed for google base currently using SQL Server 2005.

There a few problems we have, which i hope can be solved!

1. namespaces
2. grouping

My current sql is as follows:

SELECT [xml].query('
<Item xmlns:g="a">
<Title>{ data(*/*/*/Title) }</Title>
<g:id>{ sql:column("ReportingCode") }</g:id>
</Item>
')
FROM esh_xml 
where [Xml].value('(/*/*/*/Attributes/Attribute[@Description="Category"][text()="MasterMix"])[1]','nvarchar(2000)') is not null

as output with the current query I get many thousands of results rows ie

<item xmlns:g="a"><title>blah blah</title><g:id>asdasd<g:id></item>

my desired output would be

<?xml version="1.0" encoding="utf-8"?>
<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0">
      <channel>
            <title>Mastermixdigital.com Product Feed </title>
            <link> http://www.mysite.com/ </link>
            <description>mastermix latest release feed </description>
            <item xmlns:g="a">
                  <title>blah blah</title>
                  <g:id>asdasd<g:id>
            </item>
            <item xmlns:g="a">
                  <title>blah blah</title>
                  <g:id>asdasd<g:id>
            </item>
            <item xmlns:g="a">
                  <title>blah blah</title>
                  <g:id>asdasd<g:id>
            </item>
      </channel>
</rss>

Another thing I believe the execution of the query could be sped up by removing redundant nodes before searching through, and any tips on how i might go about this!

Many thanks

Answer 1

I don't have your xml structure or tables so I created a sample that shows what you can do.

Setup sample data, two rows with two items in xml for each row, one item in row one is duplicate with one item in row two.

declare @T table (ID int identity, [xml] xml)

insert into @T ([xml])
values ('
<root>
  <item>
    <id>1</id>
    <name>Name 1</name>
  </item>
  <item>
    <id>2</id>
    <name>Name 2</name>
  </item>
</root>
')

insert into @T ([xml])
values ('
<root>
  <item>
    <id>1</id>
    <name>Name 1</name>
  </item>
  <item>
    <id>3</id>
    <name>Name 3</name>
  </item>
</root>
')

Get id and name from all rows

select  
  r.i.value('id[1]', 'int') as id,
  r.i.value('name[1]', 'varchar(10)') as name
from @T as T
  cross apply T.[xml].nodes('root/item') r(i)

Result, four rows

id  name
1   Name 1
2   Name 2
1   Name 1
3   Name 3

Same query with duplicates removed

select distinct 
  r.i.value('id[1]', 'int') as id,
  r.i.value('name[1]', 'varchar(10)') as name
from @T as T
  cross apply T.[xml].nodes('root/item') r(i)  

Result three rows

id  name
1   Name 1
2   Name 2
3   Name 3

Join the rows back to xml using for xml path, root

select distinct 
  r.i.value('id[1]', 'int') as id,
  r.i.value('name[1]', 'varchar(10)') as name
from @T as T
  cross apply T.[xml].nodes('root/item') r(i)  
for xml path('item'), root('root')

Result xml

<root>
  <item>
    <id>1</id>
    <name>Name 1</name>
  </item>
  <item>
    <id>2</id>
    <name>Name 2</name>
  </item>
  <item>
    <id>3</id>
    <name>Name 3</name>
  </item>
</root>

With namespace

;with xmlnamespaces('a' as g)
select distinct 
  r.i.value('id[1]', 'int') as [g:id],
  r.i.value('name[1]', 'varchar(10)') as name
from @T as T
  cross apply T.[xml].nodes('root/item') r(i)  
for xml path('item'), root('root')

Result

<root xmlns:g="a">
  <item>
    <g:id>1</g:id>
    <name>Name 1</name>
  </item>
  <item>
    <g:id>2</g:id>
    <name>Name 2</name>
  </item>
  <item>
    <g:id>3</g:id>
    <name>Name 3</name>
  </item>
</root>