Как вставить набор строк и сгенерированный XML столбец с помощью CTE в таблицу в одном запросе

У меня есть образец таблицы сгенерированный и XML сгенерированный с использованием cte из него

declare @t table (ID int, PersonID int, Type varchar(10), Name varchar(10),                              
Category varchar(10), value int)
insert @t values
(1, 1234, 'xtype', 'John', 'abc', 200),
(2, 1234, 'ytype', 'John', 'xyz', 230),
(3, 1234, 'ztype', 'John', 'ccc', 220),
(4, 2222, 'xtype', 'Jim', 'abc', 200),
 (5, 3333, 'xtype', 'Tim', 'abc', 500)

; with cte
as (
select distinct PersonID from @t
 )
 select
(select
    PersonID,
    (select
         Type,
         Name,
         category,
         value
     from
         @t t
     where
         t.PersonID = cteInner.PersonID
     for xml path('SpecifiedType'), type ) 
from
    cte cteInner
where
    cteInner.PersonID = cteOuter.PersonID
for xml path(''), type, root('data') ) as xmldetail
from 
cte cteOuter

Теперь я хочу вставить следующие данные в другую таблицу, включая XML, я создал другие столбцы данных

  Example Table
  PersonID   Name           xmldetail
  1234       John        (GeneratedXmlFrom using cte)
  2222       Jim         (GeneratedXmlFrom using cte)
  3333       Tim         (GeneratedXmlFrom using cte)
Источник

2 ответа

Решение
declare @t table (ID int, PersonID int, Type varchar(10), Name varchar(10),                              
Category varchar(10), value int)
insert @t values
(1, 1234, 'xtype', 'John', 'abc', 200),
(2, 1234, 'ytype', 'John', 'xyz', 230),
(3, 1234, 'ztype', 'John', 'ccc', 220),
(4, 2222, 'xtype', 'Jim', 'abc', 200),
 (5, 3333, 'xtype', 'Tim', 'abc', 500)
;WITH cte
AS (
    SELECT DISTINCT PersonID
        ,NAME
    FROM @t
    )
 insert into TABLENAME
SELECT PersonID
    ,NAME
    ,(
        SELECT PersonID
            ,(
                SELECT Type
                    ,NAME
                    ,category
                    ,value
                FROM @t t
                WHERE t.PersonID = cteInner.PersonID
                FOR XML PATH('SpecifiedType')
                    ,TYPE
                )
        FROM cte cteInner
        WHERE cteInner.PersonID = cteOuter.PersonID
        FOR XML PATH('')
            ,TYPE
            ,ROOT('data')
        ) AS xmldetail
FROM cte cteOuter

Вы должны добавить имя и идентификатор в список выбора

ВЫБЕРИТЕ PersonID, Имя,

Также необходимо включить имя в операторе выбора CTE

о / р

1234    John    <data><PersonID>1234</PersonID><SpecifiedType><Type>xtype</Type><Name>John</Name><category>abc</category><value>200</value></SpecifiedType><SpecifiedType><Type>ytype</Type><Name>John</Name><category>xyz</category><value>230</value></SpecifiedType><SpecifiedType><Type>ztype</Type><Name>John</Name><category>ccc</category><value>220</value></SpecifiedType></data>
2222    Jim     <data><PersonID>2222</PersonID><SpecifiedType><Type>xtype</Type><Name>Jim</Name><category>abc</category><value>200</value></SpecifiedType></data>
3333    Tim     <data><PersonID>3333</PersonID><SpecifiedType><Type>xtype</Type><Name>Tim</Name><category>abc</category><value>500</value></SpecifiedType></data>

Чтобы вставить значение в другую таблицу, используйте insert into TABLENAME

Источник

Просто добавьте его, чтобы выбрать:

WITH    cte
          AS ( SELECT DISTINCT
                        PersonID,
                        Name                      
               FROM     @t
             )
    SELECT  PersonID, Name, ( SELECT    PersonID ,
                        ( SELECT    Type ,
                                    Name ,
                                    category ,
                                    value
                          FROM      @t t
                          WHERE     t.PersonID = cteInner.PersonID
                        FOR
                          XML PATH('SpecifiedType') ,
                              TYPE
                        )
              FROM      cte cteInner
              WHERE     cteInner.PersonID = cteOuter.PersonID
            FOR
              XML PATH('') ,
                  TYPE ,
                  ROOT('data')
            ) AS xmldetail
    FROM    cte cteOuter

Выход:

PersonID    Name    xmldetail
1234    John   <data><PersonID>1234</PersonID><Name>John</Name><SpecifiedType><Type>xtype</Type><Name>John</Name><category>abc</category><value>200</value></SpecifiedType><SpecifiedType><Type>ytype</Type><Name>John</Name><category>xyz</category><value>230</value></SpecifiedType><SpecifiedType><Type>ztype</Type><Name>John</Name><category>ccc</category><value>220</value></SpecifiedType></data>
2222    Jim    <data><PersonID>2222</PersonID><Name>Jim</Name><SpecifiedType><Type>xtype</Type><Name>Jim</Name><category>abc</category><value>200</value></SpecifiedType></data>
3333    Tim    <data><PersonID>3333</PersonID><Name>Tim</Name><SpecifiedType><Type>xtype</Type><Name>Tim</Name><category>abc</category><value>500</value></SpecifiedType></data>
Источник
Другие вопросы по тегам