CODEWITHSUNDEEP
xmlsql-server-2008
Kralizek
Kralizek

Reputation: 2073

How to merge multiple items from an Xml field in SQL Server

I'm trying to work with some xml data we have in our database.

The starting point is a table with this rough outline:

CREATE TABLE MyTable
(
    ID INT NOT NULL IDENTITY(1,1),
    ...,
    FKSiteID INT NOT NULL REFERENCES ...,
    ...,
    Keywords XML(DOCUMENT info.Keywords) NULL
)

a typical xml snippet could be:

<keywords xmlns="http://www.educations.com/Info/Keywords">
  <keyword>keyword 1</keyword>
  <keyword>keyword 2</keyword>
  <keyword>keyword 3</keyword>
  <keyword>keyword 4</keyword>
  <keyword>keyword 5</keyword>
</keywords>

what I want to achieve at the end is a view showing all the keywords grouped in a single xml document following the same schema by the value of FKSiteID.

As a middle step I was trying to extract all the keywords but I didn't manage to do it without using a table function and CROSS APPLY to it the table.

Any other hint?

Upvotes: 2

Views: 1267

Answers (2)

Kralizek
Kralizek

Reputation: 2073

By googling a bit better I found guides on msdn telling how to solve the problem.

This is the partial solution to the problem

WITH XMLNAMESPACES ('http://www.educations.com/Info/Keywords' AS a )
(
    SELECT ST.FKSiteID AS SiteID, K.Keywords.value('.','nvarchar(max)') AS Keyword
    FROM 
        info.SearchTexts ST
        CROSS APPLY ST.Keywords.nodes('a:keywords/a:keyword') AS K(Keywords)
)

I was trying to group by ST.FKSiteID and aggregate the values, but apparently there is no aggregate function working with xml data.

Such a shame.

Upvotes: 0

nathan_jr
nathan_jr

Reputation: 9302

Merging the documents is doable, however you'll be subject to a namespace bug/feature in xquery. Seems that the namespace when used with FOR XML cascades throughout all node levels. It does create valid XML, but its not as readable and totally redundant. More here

Hopefully this is what youre looking for:

declare @MyTable table (
    ID INT NOT NULL IDENTITY(1,1),
    FKSiteID INT NOT NULL,
    Keywords XML NULL
)

insert into @MyTable (FKSiteID, Keywords)
    values  (1, '<keywords xmlns="http://www.educations.com/Info/Keywords">
                  <keyword>keyword 1</keyword>
                  <keyword>keyword 2</keyword>
                  <keyword>keyword 3</keyword>
                  <keyword>keyword 4</keyword>
                  <keyword>keyword 5</keyword>
                </keywords>'
            ),
            (1, '<keywords xmlns="http://www.educations.com/Info/Keywords">
                  <keyword>keyword 6</keyword>
                  <keyword>keyword 7</keyword>
                </keywords>'),
            (2, '<keywords xmlns="http://www.educations.com/Info/Keywords">
                  <keyword>keyword 21</keyword>
                </keywords>')

-- you probably have lookup table instead of the below cte          
;with XMLNAMESPACES('http://www.educations.com/Info/Keywords' AS ns),
c_Sites (FKSiteId)
as  (   select  distinct FKSiteId 
        from    @MyTable
    )
select  FKSiteID, 
        (   select Keywords.query('ns:keywords/*')
            from    @MyTable i 
            where   i.FKSiteID = O.FKSiteId
            for xml path(''), root('keywords'), type
        )
from c_Sites o;

Upvotes: 3

Related Questions