Обновление T-SQL XML с фрагментом XML из другой связанной таблицы

Я ищу способ обновить (.modify ('вставить в ..'), чтобы быть конкретным) столбец XML в таблице с фрагментами xml из другой таблицы, связанной внешним ключом.

Например, моя структура таблицы выглядит так (упрощенно):

Поля с 1 по 5 в каждой таблице можно игнорировать; их единственная цель - объяснить поля Xx.
Поля Xx в каждой таблице определены как XML и предварительно заполнены фрагментом XML, который содержит поля из таблицы внутри тега, имя таблицы. Фрагменты XML показаны после этого списка.
Таблица B и таблица C имеют внешний ключ FK_A, который связывает их с таблицей A. Таблицы A - B и таблицы A - C относятся к одному к многим (одна запись в A может иметь несколько записей в B и C).

Теперь к примерным значениям поля Xx до того, что мне нужно достичь:

<!-- Table A record 1 -->
<TableA PK_A="1">
    <A1>Avalue</A1>
    <A2>Avalue</A2>
    <A3>Avalue</A3>
    <A4>Avalue</A4>
    <A5>Avalue</A5>
</TableA>

<!-- Table B record 1 -->
<TableB PK_B="1" FK_A="1">
    <B1>Bvalue1</B1>
    <B2>Bvalue1</B2>
    <B3>Bvalue1</B3>
    <B4>Bvalue1</B4>
    <B5>Bvalue1</B5>
</TableB>
<!-- Table B record 2 -->
<TableB PK_B="2" FK_A="1">
    <B1>Bvalue2</B1>
    <B2>Bvalue2</B2>
    <B3>Bvalue2</B3>
    <B4>Bvalue2</B4>
    <B5>Bvalue2</B5>
</TableB>

<!-- Table C record 1 -->
<TableC PK_C="1" FK_A="1">
    <C1>Cvalue1</C1>
    <C2>Cvalue1</C2>
    <C3>Cvalue1</C3>
    <C4>Cvalue1</C4>
    <C5>Cvalue1</C5>
</TableC>
<!-- Table C record 2 -->
<TableC PK_C="2" FK_A="1">
    <C1>Cvalue2</C1>
    <C2>Cvalue2</C2>
    <C3>Cvalue2</C3>
    <C4>Cvalue2</C4>
    <C5>Cvalue2</C5>
</TableC>

Проблема здесь в том, как мне обновить таблицу A, вставив все XB и XC в качестве первых (или последних) в соответствующий XA? Я предпочитаю одну операцию для обновления всей таблицы.

После операции XA должен выглядеть так:

<!-- Table A record 1 -->
<TableA PK_A="1">
    <!-- Table B record 1 -->
    <TableB PK_B="1" FK_A="1">
        <B1>Bvalue1</B1>
        <B2>Bvalue1</B2>
        <B3>Bvalue1</B3>
        <B4>Bvalue1</B4>
        <B5>Bvalue1</B5>
    </TableB>
    <!-- Table B record 2 -->
    <TableB PK_B="2" FK_A="1">
        <B1>Bvalue2</B1>
        <B2>Bvalue2</B2>
        <B3>Bvalue2</B3>
        <B4>Bvalue2</B4>
        <B5>Bvalue2</B5>
    </TableB>
    <!-- Table C record 1 -->
    <TableC PK_C="1" FK_A="1">
        <C1>Cvalue1</C1>
        <C2>Cvalue1</C2>
        <C3>Cvalue1</C3>
        <C4>Cvalue1</C4>
        <C5>Cvalue1</C5>
    </TableC>
    <!-- Table C record 2 -->
    <TableC PK_C="2" FK_A="1">
        <C1>Cvalue2</C1>
        <C2>Cvalue2</C2>
        <C3>Cvalue2</C3>
        <C4>Cvalue2</C4>
        <C5>Cvalue2</C5>
    </TableC>
    <A1>Avalue</A1>
    <A2>Avalue</A2>
    <A3>Avalue</A3>
    <A4>Avalue</A4>
    <A5>Avalue</A5>
</TableA>

Что я пробовал на данный момент?

До сих пор я лучше всего руководствовался принципом «разделяй и властвуй» (решение из двух частей, которое не работает).

WITH    CTEB (PK_A, XA, XB)
AS      (SELECT A.PK_A,
                a.XA,
                b.XB
         FROM   TableA AS a
                INNER JOIN
                TableB AS b
                ON b.FK_A = a.PK_A)
UPDATE  CTEB
    SET XA.modify('insert sql:column("XB") as last into (/TableA)[1]');

WITH    CTEC (PK_A, XA, XC)
AS      (SELECT A.PK_A,
                a.XA,
                c.XC
         FROM   TableA AS a
                INNER JOIN
                TableC AS c
                ON c.FK_A = a.PK_A)
UPDATE  CTEC
    SET XA.modify('insert sql:column("XC") as last into (/TableA)[1]');

Редактировать:

Приносим извинения за то, что не предоставили никаких текстовых значений для копирования и вставки. Вот так.

DECLARE @A TABLE (PK_A INT, XA XML);
DECLARE @B TABLE (PK_B  INT, XB  XML, FK_A INT);
DECLARE @C TABLE (PK_C  INT, XC  XML, FK_A INT);

INSERT  INTO @A
VALUES (1, '<TableA PK_A="1"><A1>Avalue</A1><A2>Avalue</A2><A3>Avalue</A3><A4>Avalue</A4><A5>Avalue</A5></TableA>')
INSERT  INTO @A
VALUES (2, '<TableA PK_A="2"><A1>Avalue</A1><A2>Avalue</A2><A3>Avalue</A3><A4>Avalue</A4><A5>Avalue</A5></TableA>')
INSERT  INTO @B
VALUES (1,'<TableB PK_B="1" FK_A="1"><B1>Bvalue1</B1><B2>Bvalue1</B2><B3>Bvalue1</B3><B4>Bvalue1</B4><B5>Bvalue1</B5></TableB>', 1)
INSERT  INTO @B
VALUES (2, '<TableB PK_B="2" FK_A="1"><B1>Bvalue2</B1><B2>Bvalue2</B2><B3>Bvalue2</B3><B4>Bvalue2</B4><B5>Bvalue2</B5></TableB>', 1)
INSERT  INTO @B
VALUES (3, '<TableB PK_B="3" FK_A="2"><B1>Bvalue3</B1><B2>Bvalue3</B2><B3>Bvalue3</B3><B4>Bvalue3</B4><B5>Bvalue3</B5></TableB>', 2)
INSERT  INTO @B
VALUES (4, '<TableB PK_B="4" FK_A="2"><B1>Bvalue4</B1><B2>Bvalue4</B2><B3>Bvalue4</B3><B4>Bvalue4</B4><B5>Bvalue4</B5></TableB>', 2)
INSERT  INTO @C
VALUES (1, '<TableC PK_C="1" FK_A="1"><C1>Cvalue1</C1><C2>Cvalue1</C2><C3>Cvalue1</C3><C4>Cvalue1</C4><C5>Cvalue1</C5></TableC>', 1)
INSERT  INTO @C
VALUES (2, '<TableC PK_C="2" FK_A="1"><C1>Cvalue2</C1><C2>Cvalue2</C2><C3>Cvalue2</C3><C4>Cvalue2</C4><C5>Cvalue2</C5></TableC>', 1)
INSERT  INTO @C
VALUES (3, '<TableC PK_C="3" FK_A="2"><C1>Cvalue3</C1><C2>Cvalue3</C2><C3>Cvalue3</C3><C4>Cvalue3</C4><C5>Cvalue3</C5></TableC>', 2)
INSERT  INTO @C
VALUES (4, '<TableC PK_C="4" FK_A="2"><C1>Cvalue4</C1><C2>Cvalue4</C2><C3>Cvalue4</C3><C4>Cvalue4</C4><C5>Cvalue4</C5></TableC>', 2);

WITH    CTEB (PK_A, XA, XB)
AS      (SELECT A.PK_A,
                a.XA,
                b.XB
         FROM   @A AS a, @B as b
         WHERE  b.FK_A = a.PK_A)
UPDATE  CTEB
    SET XA.modify('insert sql:column("XB") as first into (/TableA)[1]');

WITH    CTEC (PK_A, XA, XC)
AS      (SELECT A.PK_A,
                a.XA,
                c.XC
         FROM   @A AS a
                INNER JOIN
                @C AS c
                ON c.FK_A = a.PK_A)
UPDATE  CTEC
    SET XA.modify('insert sql:column("XC") as first into (/TableA)[1]');

SELECT * FROM @A;

Это обновляет XML только с первым фрагментом XML из таблиц B и C.

Обновление: всем, кто может столкнуться с этим вопросом, просмотрите ответ @Shnugo вместе с отмеченным ответом. Оба подхода идеальны. Я отметил решение @ gofr1 как ответ просто потому, что он ответил первым. Еще одно соображение для будущих охотников за квестами - нравится ли вам CTE или sub-select (как указал Шнуго).

Jayachandran 27.10.2016 источник

comment

В будущем, пожалуйста, добавляйте xml-фрагменты напрямую. Не делайте снимок экрана и не публикуйте его. Люди хотят копировать / вставлять эти xml-фрагменты, чтобы поиграть с ними, они предпочли бы не печатать их сами. GL. - TT. 27.10.2016

comment

К сожалению, добавлен образец структуры таблицы и данных для работы. - Jayachandran 27.10.2016

comment

Никаких извинений :). Это вам подсказка; люди будут более охотно помогать, если им не нужно набирать пример xml-фрагмента, чтобы начать работу над ответом. - TT. 27.10.2016

comment

Теперь, после всех ваших улучшений, это отличный вопрос! +1 с моей стороны - Shnugo 27.10.2016

Ответы (2)

arrow_upward
3
arrow_downward

У меня нет связи между таблицами, поэтому я немного жестко запрограммировал это (взято из XML). Вот какое-то решение, которое работает с тестовыми образцами (в следующий раз, пожалуйста, вставьте актуальные данные, а не скриншоты).

Создание таблиц и вставка данных, похожих на ваши:

USE tempdb

IF OBJECT_ID(N'#table_a') IS NOT NULL DROP TABLE #table_a
IF OBJECT_ID(N'#table_b') IS NOT NULL DROP TABLE #table_b
IF OBJECT_ID(N'#table_c') IS NOT NULL DROP TABLE #table_c

CREATE TABLE #table_a (
    A nvarchar(2),
    Xx xml
)

CREATE TABLE #table_b (
    B nvarchar(2),
    Xx xml
)

CREATE TABLE #table_c (
    C nvarchar(2),
    Xx xml
)

INSERT INTO #table_a VALUES
(N'A1', N'<tablea PK_A="1"><A1>Avalue1</A1><A2>Avalue2</A2><A3>Avalue3</A3><A4>Avalue4</A4><A5>Avalue5</A5></tablea>')

INSERT INTO #table_b VALUES
(N'B1', N'<tableb PK_B="1" FK_A="1"><B1>Bvalue11</B1><B2>Bvalue12</B2><B3>Bvalue13</B3><B4>Bvalue14</B4><B5>Bvalue15</B5></tableb>'),
(N'B2', N'<tableb PK_B="2" FK_A="1"><B1>Bvalue21</B1><B2>Bvalue22</B2><B3>Bvalue23</B3><B4>Bvalue24</B4><B5>Bvalue25</B5></tableb>')

INSERT INTO #table_c VALUES
(N'C1', N'<tablec PK_C="1" FK_A="1"><C1>Cvalue11</C1><C2>Cvalue12</C2><C3>Cvalue13</C3><C4>Cvalue14</C4><C5>Cvalue15</C5></tablec>'),
(N'C2', N'<tablec PK_C="2" FK_A="1"><C1>Cvalue21</C1><C2>Cvalue22</C2><C3>Cvalue23</C3><C4>Cvalue24</C4><C5>Cvalue25</C5></tablec>')

Обновление:

UPDATE a
set Xx.modify('insert sql:column("b.Xx") as first into (/tablea)[1]')
FROM #table_a a
OUTER APPLY (SELECT (
    SELECT Xx 
    FROM #table_b
    WHERE a.Xx.value('(/tablea/@PK_A)[1]','int') = Xx.value('(/tableb/@FK_A)[1]','int')
    FOR XML PATH(''), TYPE).query('/Xx/tableb') as Xx
    ) b

UPDATE a
set Xx.modify('insert sql:column("c.Xx") as first into (/tablea)[1]')
FROM #table_a a
OUTER APPLY (SELECT (
    SELECT Xx 
    FROM #table_c
    WHERE a.Xx.value('(/tablea/@PK_A)[1]','int') = Xx.value('(/tablec/@FK_A)[1]','int')
    FOR XML PATH(''), TYPE).query('/Xx/tablec') as Xx
    ) c

SELECT *
FROM #table_a

И вывод:

<tablea PK_A="1">
  <tablec PK_C="1" FK_A="1">
    <C1>Cvalue11</C1>
    <C2>Cvalue12</C2>
    <C3>Cvalue13</C3>
    <C4>Cvalue14</C4>
    <C5>Cvalue15</C5>
  </tablec>
  <tablec PK_C="2" FK_A="1">
    <C1>Cvalue21</C1>
    <C2>Cvalue22</C2>
    <C3>Cvalue23</C3>
    <C4>Cvalue24</C4>
    <C5>Cvalue25</C5>
  </tablec>
  <tableb PK_B="1" FK_A="1">
    <B1>Bvalue11</B1>
    <B2>Bvalue12</B2>
    <B3>Bvalue13</B3>
    <B4>Bvalue14</B4>
    <B5>Bvalue15</B5>
  </tableb>
  <tableb PK_B="2" FK_A="1">
    <B1>Bvalue21</B1>
    <B2>Bvalue22</B2>
    <B3>Bvalue23</B3>
    <B4>Bvalue24</B4>
    <B5>Bvalue25</B5>
  </tableb>
  <A1>Avalue1</A1>
  <A2>Avalue2</A2>
  <A3>Avalue3</A3>
  <A4>Avalue4</A4>
  <A5>Avalue5</A5>
</tablea>

ИЗМЕНИТЬ

ОБНОВЛЕНИЕ одной части:

UPDATE a
set Xx.modify('insert sql:column("b.Xx") as first into (/tablea)[1]')
FROM #table_a a
OUTER APPLY (SELECT (
    SELECT * 
    FROM (
            SELECT Xx
            FROM #table_b b
            WHERE a.Xx.value('(/tablea/@PK_A)[1]','int') = b.Xx.value('(/tableb/@FK_A)[1]','int') 
            UNION ALL
            SELECT Xx 
            FROM #table_c c
            WHERE a.Xx.value('(/tablea/@PK_A)[1]','int') = c.Xx.value('(/tablec/@FK_A)[1]','int')
        ) d
    FOR XML PATH(''), TYPE).query('/Xx/*') as Xx
    ) b

gofr1 27.10.2016

comment

Плюс голосование, потому что это решение работает. Однако это снова решение из двух частей. Так что буду ждать других решений, чтобы увидеть, как они пойдут. Большое спасибо. По крайней мере, у меня есть запасной вариант. И это великолепно. - Jayachandran; 27.10.2016

comment

И с удовольствием помогу! ;) - gofr1; 27.10.2016

comment

@ gofr1 Если вы добавите AS [node()] в свой SELECT Xx, вам не понадобится дополнительный .query('/Xx/*'). Единственная разница между этим и моим ответом - CTE или sub-select. Кажется, дело вкуса :-) В любом случае +1 с моей стороны, так как вам пришлось создать свой собственный тестовый сценарий, а я мог просто скопировать существующий код из вопроса. - Shnugo; 27.10.2016

comment

@Shnugo Не слышал о SELECT [column] as [node()], не могли бы вы дать несколько советов / примеров (возможно, ссылки) использования этого материала? - gofr1; 27.10.2016

comment

@ gofr1, Посмотрите сюда, особенно столбцы с именем теста узла XPath . AS [node()] примерно такой же, как AS [*]. Он вставит значение без имени столбца. В связи с XML-экземплярами я предпочитаю AS [node()], чтобы акцентировать внимание на том факте, что XML вставлен. - Shnugo; 27.10.2016

comment

@Shnugo Отлично! Спасибо! - gofr1; 27.10.2016

comment

Хотел бы я отметить несколько ответов как правильные. Потому что подход @Shnugo отличается, но тоже идеален. Так что, честно говоря, я выберу тот, который был опубликован первым :). - Jayachandran; 31.10.2016

arrow_upward
3
arrow_downward

Оператор XML-DML .modify() допускает одно изменение за раз. Поскольку вы хотите вставить два фрагмента в XML в таблице A и предпочитаете одну операцию для обновления всей таблицы, я бы предложил сначала подготовить комбинированное значение и вставить его за один вызов. Следующий код полностью ad-hoc ...

Попробуйте вот так

WITH FragmentsToAdd AS
(
    SELECT tblA.PK_A
          ,(
            SELECT *
            FROM
            (
                SELECT tblB.XB AS [node()]
                FROM @B AS tblB
                WHERE tblB.FK_A=tblA.PK_A
                UNION ALL
                SELECT tblC.XC AS [node()]
                FROM @C AS tblC
                WHERE tblC.FK_A=tblA.PK_A
            ) AS x
            FOR XML PATH(''),TYPE
           ) Fragment
    FROM @A AS tblA 
    GROUP BY tblA.PK_A
)
UPDATE @A SET XA.modify('insert sql:column("Fragment") as first into /TableA[1]')
FROM @A AS a
INNER JOIN FragmentsToAdd AS fr ON a.PK_A=fr.PK_A;

SELECT * FROM @A

Shnugo 27.10.2016

comment

Мне нравится это решение, потому что в реальной проблеме у меня есть более 20 таблиц, связанных с основной таблицей, и есть несколько основных таблиц. Это дает мне возможность иметь один запрос (который скоро будет хранимым процессом) для каждой основной таблицы. И в будущем я могу продолжать добавлять таблицы с помощью UNION ALL, чтобы продолжать расширяться. Подождите еще некоторое время, прежде чем отмечать как ответ. - Jayachandran; 27.10.2016

Обновление T-SQL XML с фрагментом XML из другой связанной таблицы

Ответы (2)

Вопросы по теме