Как объединить несколько элементов из поля Xml в SQL Server

#xml #sql-server-2008

Вопрос:

Я пытаюсь работать с некоторыми XML-данными, которые у нас есть в нашей базе данных.

Отправной точкой является таблица с таким приблизительным описанием:

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

типичный фрагмент xml может быть:

 <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>

чего я хочу добиться в конце, так это представления, показывающего все ключевые слова, сгруппированные в одном xml-документе в соответствии с одной и той же схемой по значению FKSiteID.

В качестве среднего шага я пытался извлечь все ключевые слова, но мне не удалось сделать это без использования табличной функции и ПЕРЕКРЕСТНОГО ПРИМЕНЕНИЯ к ней таблицы.

Есть еще какие-нибудь подсказки?

1. Если у вас есть список XML-узлов, из которых вы хотите извлечь данные, я не думаю, что там есть что-то еще, кроме CROSS APPLY …..

2. да, я заметил, но мне интересно, есть ли какой-либо способ избежать создания определенного TF для этого запроса…

Ответ №1:

Объединение документов выполнимо, однако вы будете подвержены ошибке / функции пространства имен в xquery. Кажется, что пространство имен при использовании с FOR XML каскадируется на всех уровнях узла. Это создает допустимый XML, но он не такой читаемый и полностью избыточный. Подробнее здесь

Надеюсь, это то, что вы ищете:

 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;

Ответ №2:

Немного поискав в Google, я нашел руководства по msdn, в которых рассказывается, как решить проблему.

Это частичное решение проблемы

 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)
)

Я пытался сгруппировать по ST.FKSiteID и объединить значения, но, по-видимому, нет агрегирующей функции, работающей с данными xml.

Такой позор.