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

Question

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?

If you have a list of XML nodes you want to extract data from, I don't think there's anything else out there beside CROSS APPLY ..... — marc_s
– marc_s, Commented May 10, 2011 at 16:28
yeah I noticed, but I wonder if there is any way to avoid creating a specific TF for this query... — Kralizek
– Kralizek, Commented May 10, 2011 at 16:45

nathan_jr · Accepted Answer · 2011-05-10 18:52:08Z

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;

even if I'm actually using the solution I posted below, your post answers perfectly to the question so you deserve the mark ;)

Kralizek · Accepted Answer · 2011-05-11 16:02:50Z

0

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.

answered May 11, 2011 at 16:02

Kralizek

2,0831 gold badge29 silver badges50 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related