노드를 반복 SQL Server 용의 XML 경로 메이크업

나는 SQL 서버 2012을 사용하여 다음과 같은 출력을 생성하고 싶습니다 :

<parent>
  <item>1</item>
  <item>2</item>
  <item>3</item>
</parent>

같은 테이블에 세 가지 다른 열에서 (우리는 그들 COL1, COL2 및 COL3 전화 할게).

나는이 쿼리를 사용하려고 해요 :

SELECT 
  t.col1 as 'item'
 ,t.col2 as 'item'
 ,t.col3 as 'item' 
FROM tbl t 
FOR XML PATH('parent'), TYPE

하지만 내가 할 것은 이것이다 :

<parent>
  <item>123</item>
</parent>

어떻게 내가 여기 잘못 한거야?

해결법

1. ==============================

   1.각 열에 대한 별도의 항목 노드를 생성하는 값으로 NULL로 컬럼을 추가합니다.

   각 열에 대한 별도의 항목 노드를 생성하는 값으로 NULL로 컬럼을 추가합니다.

   SELECT 
     t.col1 as 'item'
    ,NULL
    ,t.col2 as 'item'
    ,NULL
    ,t.col3 as 'item' 
   FROM dbo.tbl as t 
   FOR XML PATH('parent'), TYPE;
   

   결과:

   <parent>
     <item>1</item>
     <item>2</item>
     <item>3</item>
   </parent>
   

   SQL 바이올린

   왜이 일을 하는가?

   이름없는 열은 텍스트 노드로 삽입됩니다. 이 경우에 NULL 값은 항목 노드 사이의 텍스트 노드로 삽입됩니다.

   당신이 NULL 대신 실제 값을 추가하는 경우 당신은 무슨 일이 일어나고 있는지 볼 수 있습니다.

   SELECT 
     t.col1 as 'item'
    ,'1'
    ,t.col2 as 'item'
    ,'2'
    ,t.col3 as 'item' 
   FROM dbo.tbl as t 
   FOR XML PATH('parent'), TYPE;
   

   결과:

   <parent>
     <item>1</item>1<item>2</item>2<item>3</item></parent>
   

   이름없는 열을 지정하는 또 다른 방법은 열 별칭으로 와일드 카드 문자 *를 사용하는 것입니다.

   와일드 카드 문자로 지정된 이름으로 열

   NULL 값을 갖는 열이 열 이름이 없기 때문에이 경우에는 와일드 카드를 사용할 필요가 없습니다하지만 실제 컬럼의 값을 원하지만 당신이 열 이름은 노드 이름 싶지 않아 때 유용합니다.

2. ==============================

   2.좋아, 당신은 그것을 위해 경로를 사용할 수 없습니다. 대신, 명시 적으로 사용하여,

   좋아, 당신은 그것을 위해 경로를 사용할 수 없습니다. 대신, 명시 적으로 사용하여,

   SELECT 1 AS tag,NULL AS parent, t.col1 AS [Parent!1!Item!element],
                  t.col2 AS [Parent!1!Item!element],
                  t.col3 AS [Parent!1!Item!element]
   FROM tbl t
   FOR XML EXPLICIT
   

3. ==============================

   3.는 XML 경로 구문을 사용하여이 문제를 해결하기위한 몇 가지 방법이 실제로있다.

   는 XML 경로 구문을 사용하여이 문제를 해결하기위한 몇 가지 방법이 실제로있다.

   첫 번째는, 예를 들어, 첫 번째 결과를 피벗 해제하는 것입니다 :

   SELECT item as [text()]
   FROM 
      (select col1, col2, col3 from tbl) p
   UNPIVOT
      (item FOR colHeading IN (col1, col2, col3)) AS unpvt
   FOR XML PATH ('item'), ROOT ('parent')
   

   제 2 회는 UNPIVOT을 필요로하지만, 더 쿼리의 반복하지 않습니다

   select (select col1 as [text()] from tbl for xml path('item'), type)
       ,  (select col2 as [text()] from tbl for xml path('item'), type)
       ,  (select col3 as [text()] from tbl for xml path('item'), type)
   for xml path ('parent')
   

   이 두 가지 모두 같은 부모 노드 아래에 여러 개의 데이터 행을 결합합니다. 당신이 2 행, 1,2,3 및 제 4,5,6 가진 첫번째가 있다면 예를 들어, 당신은 얻을 것이다 :

   <parent>
       <item>1</item>
       <item>2</item>
       <item>3</item>
       <item>4</item>
       <item>5</item>
       <item>6</item>
   </parent>
   

   대신, 각 행을 원하는 경우에 당신은 당신이 각 행에 대한 몇 가지 행 식별자가 가정, 다음 행마다 고유의 부모 요소를 가지고 UNPIVOT 이러한 접근 방식을 조정하여 해당 행에 의해 그룹, 당신이 할 수있는 이러한 (나는 그것을 parentId 전화 할게) :

   SELECT
     (
         SELECT item as [text()]
         FROM 
           (select parentId, col1, col2, col3 from tbl tt where tt.parentid =   t.parentid) p
         UNPIVOT
           (item FOR colHeading IN (col1, col2, col3)) AS unpvt
         FOR XML PATH ('item'), TYPE
     )
   FROM tbl t
   FOR XML PATH ('parent')
   

   또는

   select (select col1 as [text()] from tbl tt where t.parentid = tt.parentid for xml path('item'), type)
       ,  (select col2 as [text()] from tbl tt where t.parentid = tt.parentid for xml path('item'), type)
       ,  (select col3 as [text()] from tbl tt where t.parentid = tt.parentid for xml path('item'), type)
   from tbl t
   for xml path ('parent')
   

   에서 발생할 것이다 :

   <parent>
       <item>1</item>
       <item>2</item>
       <item>3</item>
   </parent>
   <parent>
       <item>4</item>
       <item>5</item>
       <item>6</item>
   </parent>
   

   데모와는 SQL 바이올린

4. ==============================

   4.여기 몇 주 : XML을 명시 적으로 사용하는 경우 (I 여전히 허용 대답을 떠날거야, 그래서 내가 언급하지 않았다 요구 사항), 당신은 WITH XMLNAMESPACES를 사용할 수 없습니다. Iheria의 대답은 매우 도움이 동안, 나는 깨달았다 이후로했습니다 다른 간단한 가능성이있다 :

   여기 몇 주 : XML을 명시 적으로 사용하는 경우 (I 여전히 허용 대답을 떠날거야, 그래서 내가 언급하지 않았다 요구 사항), 당신은 WITH XMLNAMESPACES를 사용할 수 없습니다. Iheria의 대답은 매우 도움이 동안, 나는 깨달았다 이후로했습니다 다른 간단한 가능성이있다 :

   SELECT CONVERT(XML, '<item>' + t.col1 
              + '</item><item>' + t.col2 
              + '</item><item>' + t.col3 + '</item>')
   FROM tbl t
   FOR XML PATH('parent'), TYPE
   

   나는 이것이 아마 가장 쉽고 성능이 좋은 방법 (나는 그것을 벤치마킹하지 않은, 아무것도하지만, 여러 옵션을 선택 가능성이 어쨌든 엔진이에 리팩토링하면 내가 더 빨리 될 것 UNPIVOT을 사용하여 상상할 수없는)라고 생각합니다.

5. ==============================

   5.당신이 일을해야이 같은 열 별칭을 변경하면 나는 생각한다. 별명은 동일한 데이터의 데이터 타입뿐만 아니라 동일 할 수 있기 때문이다. 당신이 COL1, COL2와 COL3에 다른 데이터가있는 경우 경우,이 동작을 표시 할 수 없습니다.

   당신이 일을해야이 같은 열 별칭을 변경하면 나는 생각한다. 별명은 동일한 데이터의 데이터 타입뿐만 아니라 동일 할 수 있기 때문이다. 당신이 COL1, COL2와 COL3에 다른 데이터가있는 경우 경우,이 동작을 표시 할 수 없습니다.

   SELECT 
   t.col1 as 'item'
   ,t.col2 as 'item1'
   ,t.col3 as 'item2' 
   FROM tbl t 
   FOR XML PATH('parent'), TYPE
   

from https://stackoverflow.com/questions/29088506/sql-server-for-xml-path-make-repeating-nodes by cc-by-sa and MIT license