예: PATH 모드 사용

아티클
07/12/2024

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance

아래의 예제는 PATH 모드를 사용해 SELECT 쿼리에서 XML을 생성하는 방법을 보여줍니다. 이러한 쿼리는 대부분 ProductModel 테이블의 Instructions 열에 저장된 자전거 제조 지침 XML 문서에 대해 지정됩니다.

PATH 모드 쿼리 지정하기

이 쿼리는 FOR XML PATH 모드를 지정합니다.

USE AdventureWorks2022;
GO
SELECT
       ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH;
GO

다음의 결과는 결과 행 집합의 각 열 값이 요소에 래핑되는 요소 중심 XML입니다. SELECT절이 열 이름에 대한 별칭을 지정하지 않으므로 생성된 자식 요소 이름은 SELECT절의 상응하는 열 이름과 같습니다. 행 집합의 각 행에 <row> 태그를 추가합니다.

<row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>

다음의 결과는 ELEMENTS 옵션을 지정해 수행된 RAW 모드 쿼리와 같습니다. 이러한 결과는 결과 집합의 각 행에 기본 <row> 요소가 있는 요소 중심 XML을 반환합니다.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML RAW, ELEMENTS;

선택적으로 행 요소 이름을 지정해 기본 <row>를 덮어쓸 수 있습니다. 예를 들어, 다음 쿼리는 행 집합의 각 행에 <ProductModel> 요소를 반환합니다.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModel');
GO

결과 XML은 지정된 행 요소 이름을 갖습니다.

<ProductModel>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</ProductModel>
<ProductModel>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</ProductModel>

빈 문자열을 지정하면 래핑 요소가 산출되지 않습니다.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('');
GO

다음은 결과입니다.

<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>

XPath 형식의 열 이름 지정하기

아래 쿼리에서 지정된 열 이름 ProductModelID은 '@'로 시작하고 슬래시 표시('/')를 포함하지 않습니다. 따라서 상응하는 열 값을 포함하는 <row> 요소의 특성이 결과 XML에 생성됩니다.

USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@id",
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('ProductModelData');
GO

다음은 결과입니다.

<ProductModelData id="122">
  <Name>All-Purpose Bike Stand</Name>
</ProductModelData>
<ProductModelData id="119">
  <Name>Bike Wash</Name>
</ProductModelData>

root 에 FOR XML옵션을 지정하여 최상위 요소 하나를 추가할 수 있습니다.

SELECT ProductModelID AS "@id",
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModelData'), root ('Root');
GO

계층 구조를 생성하기 위해 PATH 형식 구문을 포함할 수 있습니다. 예를 들어, Name 열의 열 이름을 "SomeChild/ModelName"으로 변경하면 다음 결과와 같이 계층 구조가 있는 XML이 생성됩니다.

<Root>
  <ProductModelData id="122">
    <SomeChild>
      <ModelName>All-Purpose Bike Stand</ModelName>
    </SomeChild>
  </ProductModelData>
  <ProductModelData id="119">
    <SomeChild>
      <ModelName>Bike Wash</ModelName>
    </SomeChild>
  </ProductModelData>
</Root>

제품 모델 ID와 이름 외에도 다음 쿼리는 제품 모델의 제조지시 위치를 검색합니다. 지시 열이 xml 형식이므로 xml 데이터 형식의 query() 메서드를 지정해 위치를 검색합니다.

SELECT ProductModelID AS "@id",
       Name,
       Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
                /MI:root/MI:Location
              ') AS ManuInstr
FROM Production.ProductModel
WHERE ProductModelID = 7
FOR XML PATH ('ProductModelData'), root ('Root');
GO

다음은 결과의 일부입니다. 쿼리에서 ManuInstr가 열 이름으로 지정되었으므로 query() 메서드에서 반환된 XML은 다음에서 보는 바와 같이 <ManuInstr> 태그에 래핑됩니다.

<Root>
  <ProductModelData id="7">
    <Name>HL Touring Frame</Name>
    <ManuInstr>
      <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
        <MI:step>...</MI:step>...
      </MI:Location>
      ...
    </ManuInstr>
  </ProductModelData>
</Root>

이전 FOR XML 쿼리에 <Root>과 <ProductModelData> 요소의 네임스페이스를 포함하고 싶을 수도 있습니다. 그럴 경우에는 먼저 FOR XML 쿼리에서 WITH XMLNAMESPACES와 접두사를 사용해 네임스페이스 바인딩의 접두사를 정의합니다. 더 자세한 내용은 WITH XMLNAMESPACES를 사용해 쿼리에 네임스페이스 추가하기 참조.

USE AdventureWorks2022;
GO
WITH XMLNAMESPACES (
   'uri1' AS ns1,
   'uri2' AS ns2,
   'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as MI)
SELECT ProductModelID AS "ns1:ProductModelID",
       Name           AS "ns1:Name",
       Instructions.query('
                /MI:root/MI:Location
              ')
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH ('ns2:ProductInfo'), root('ns1:root');
GO

MI 접두사는 WITH XMLNAMESPACES에서도 정의됩니다. 결과적으로, 지정된 xml 형식의 query() 메서드는 쿼리 프롤로그에서 접두사를 정의하지 않습니다. 다음은 결과입니다.

<ns1:root xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" xmlns="uri2" xmlns:ns2="uri2" xmlns:ns1="uri1">
  <ns2:ProductInfo>
    <ns1:ProductModelID>7</ns1:ProductModelID>
    <ns1:Name>HL Touring Frame</ns1:Name>
    <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5" LocationID="10" xmlns="">
    <MI:step>
      Insert <MI:material>aluminum sheet MS-2341</MI:material> into the <MI:tool>T-85A framing tool</MI:tool>.
    </MI:step>
    ...
    </MI:Location>
    ...
  </ns2:ProductInfo>
</ns1:root>

PATH 모드를 사용해 값 목록 생성하기

이 쿼리는 각 제품 모델에 대해 제품 ID의 값 목록을 생성합니다. 아래 XML 조각에서 보는 바와 같이 이 쿼리는 또한 각 제품 ID에 대해 중첩된 요소 <ProductName>를 생성합니다.

<ProductModelData ProductModelID="7" ProductModelName="..." ProductIDs="product id list in the product model">
  <ProductName>...</ProductName>
  <ProductName>...</ProductName>
  ...
</ProductModelData>

원하는 XML을 산출하는 쿼리는 다음과 같습니다.

USE AdventureWorks2022;
GO
SELECT ProductModelID     AS "@ProductModelID",
       Name               AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')) AS "@ProductIDs",
       (SELECT Name AS "ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
        FOR XML PATH ('')) AS "ProductNames"
FROM   Production.ProductModel
WHERE  ProductModelID= 7 or ProductModelID=9
FOR XML PATH('ProductModelData');

이전 쿼리의 다음 사항에 유의하세요.

첫 번째 중첩된 SELECT은 data()를 열 이름으로 사용해 ProductIDs 목록을 반환합니다. 쿼리에서 빈 문자열이 FOR XML PATH의 행 요소 이름으로 지정되었으므로 어떠한 요소도 생성되지 않습니다. 대신 값 목록이 ProductID 특성에 할당됩니다.
두 번째 중첩된 SELECT은 제품 모델에서 제품명을 검색합니다. ProductNames가 열 이름으로 지정되었으므로 쿼리는 <ProductName> 요소를 생성하며, 이렇게 반환된 요소는 <ProductNames> 요소에 래핑됩니다.

다음은 결과의 일부입니다.

<ProductModelData PId="7" ProductModelName="HL Touring Frame" ProductIDs="885 887 ...">
  <ProductNames>
    <ProductName>HL Touring Frame - Yellow, 60</ProductName>
    <ProductName>HL Touring Frame - Yellow, 46</ProductName>
  </ProductNames>
  ...
</ProductModelData>
<ProductModelData PId="9" ProductModelName="LL Road Frame" ProductIDs="722 723 724 ...">
  <ProductNames>
    <ProductName>LL Road Frame - Black, 58</ProductName>
    <ProductName>LL Road Frame - Black, 60</ProductName>
    <ProductName>LL Road Frame - Black, 62</ProductName>
    ...
  </ProductNames>
</ProductModelData>

제품명을 생성하는 하위 쿼리는 엔터티화된 뒤 XML에 추가되는 문자열로 결과를 반환합니다. type 지시어 FOR XML PATH (''), type을 추가하면 하위 쿼리가 xml 유형으로 결과를 반환하며 수정되지 않습니다.

USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@ProductModelID",
      Name AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')
       ) AS "@ProductIDs",
       (
       SELECT Name AS "ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH (''), type
       ) AS "ProductNames"

FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData');

결과 XML에 네임스페이스 추가하기

WITH XMLNAMESPACES를 사용해 네임스페이스 추가하기에서 설명한 바와 같이 WITH XMLNAMESPACES를 사용해 PATH 모드 쿼리에 네임스페이스를 포함할 수 있습니다. 예를 들어 SELECT 절에 지정된 이름에는 네임스페이스 접두사가 포함됩니다. 다음의 PATH 모드 쿼리는 네임스페이스를 사용해 XML을 생성합니다.

SELECT 'en'    as "English/@xml:lang",
       'food'  as "English",
       'ger'   as "German/@xml:lang",
       'Essen' as "German"
FOR XML PATH ('Translation')
GO

@xml:lang 요소에 추가되는 <English> 특성은 사전 정의된 xml 네임스페이스에서 정의됩니다.

다음은 결과입니다.

<Translation>
  <English xml:lang="en">food</English>
  <German xml:lang="ger">Essen</German>
</Translation>

다음 쿼리는 WITH XMLNAMESPACES을 사용해 XML 결과에 네임스페이스를 포함한다는 점을 제외하면 예제 C와 유사합니다. 더 자세한 내용은 WITH XMLNAMESPACES를 사용해 쿼리에 네임스페이스 추가하기 참조.

USE AdventureWorks2022;
GO
WITH XMLNAMESPACES ('uri1' AS ns1,  DEFAULT 'uri2')
SELECT ProductModelID AS "@ns1:ProductModelID",
      Name AS "@ns1:ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')
       ) AS "@ns1:ProductIDs",
       (
       SELECT ProductID AS "@ns1:ProductID",
              Name AS "@ns1:ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH , type
       ) AS "ns1:ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData'), root('root');

다음은 결과입니다.

<root xmlns="uri2"
  xmlns:ns1="uri1">
  <ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />
      ...
    </ns1:ProductNames>
  </ProductModelData>
  <ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />
      ...
    </ns1:ProductNames>
  </ProductModelData>
</root>

참고 항목

FOR XML에서 PATH 모드 사용

다음을 통해 공유

예: PATH 모드 사용

PATH 모드 쿼리 지정하기

XPath 형식의 열 이름 지정하기

PATH 모드를 사용해 값 목록 생성하기

결과 XML에 네임스페이스 추가하기

참고 항목

피드백

추가 리소스