예: XMLType 열 쿼리Example: Querying XMLType Columns

다음 쿼리에는 xml 유형의 열이 포함됩니다.The following query includes columns of xml type. 이 쿼리는 Instructions xml 유형의 열로부터 첫 번째 위치에서 제품 모델 ID, 이름 및 제조 단계를 검색합니다.The query retrieves product model ID, name, and manufacturing steps at the first location from the Instructions column of the xml type.

예제Example

USE AdventureWorks2012;  
GO  
SELECT ProductModelID, Name,  
   Instructions.query('  
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"  
   /MI:root/MI:Location[1]/MI:step  
')   
FROM Production.ProductModel  
FOR XML RAW ('ProductModelData')  
GO  

다음은 결과입니다.The following is the result. 테이블에는 일부 제품 모델에 대해서만 제조 지침이 저장되어 있습니다.Note that the table stores manufacturing instructions for only some product models. 제조 단계는 <ProductModelData> 요소의 하위 요소로 결과에 반환됩니다.The manufacturing steps are returned as subelements of the <ProductModelData> element in the result.

<ProductModelData ProductModelID="5" Name="HL Mountain Frame" />  
<ProductModelData ProductModelID="6" Name="HL Road Frame" />  
<ProductModelData ProductModelID="7" Name="HL Touring Frame">  
    <MI:step> ... </MI:step>  
    <MI:step> ... </MI:step>  
 </ProductModelData>  

다음 SELECT 문에 지정된 것과 같이 쿼리가 XQuery에 의해 반환된 XML에 대한 열 이름을 지정하는 경우 제조 단계는 지정된 이름이 포함된 요소에 래핑됩니다.If the query specifies a column name for the XML returned by the XQuery, as specified in the following SELECT statement, the manufacturing steps are wrapped in the element that has the specified name.

USE AdventureWorks2012;  
GO  
SELECT ProductModelID, Name,  
   Instructions.query('  
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"  
   /MI:root/MI:Location[1]/MI:step  
') as ManuSteps  
FROM Production.ProductModel  
FOR XML RAW ('ProductModelData')  
go  

다음은 결과입니다.This is the result:

<ProductModelData ProductModelID="5" Name="HL Mountain Frame" />  
<ProductModelData ProductModelID="6" Name="HL Road Frame" />  
<ProductModelData ProductModelID="7" Name="HL Touring Frame">  
  <ManuSteps>  
    <MI:step ... </MI:step>  
    <MI:step ... </MI:step>  
  </ManuSteps>  
</ProductModelData>  

다음 쿼리는 ELEMENTS 지시어를 지정합니다.The following query specifies the ELEMENTS directive. 따라서 반환된 결과는 요소 중심입니다.Therefore, the result returned is element-centric. XSINIL 지시어와 함께 지정된 ELEMENTS 옵션은 행 집합의 해당 열이 NULL인 경우에도 <ManuSteps> 요소를 반환합니다.The XSINIL option specified with the ELEMENTS directive returns the <ManuSteps> elements, even if the corresponding column in the rowset is NULL.

USE AdventureWorks2012;  
GO  
SELECT ProductModelID, Name,  
   Instructions.query('  
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"  
   /MI:root/MI:Location[1]/MI:step  
') as ManuSteps  
FROM Production.ProductModel  
FOR XML RAW ('ProductModelData'), root('MyRoot'), ELEMENTS XSINIL  
go  

다음은 결과입니다.This is the result:

<MyRoot xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
   ...  
  <ProductModelData>  
    <ProductModelID>6</ProductModelID>  
    <Name>HL Road Frame</Name>  
    <ManuSteps xsi:nil="true" />  
  </ProductModelData>  
  <ProductModelData>  
    <ProductModelID>7</ProductModelID>  
    <Name>HL Touring Frame</Name>  
    <ManuSteps>  
      <MI:step ... </MI:step>  
      <MI:step ...</MI:step>  
       ...  
    </ManuSteps>  
  </ProductModelData>  
</MyRoot>  

참고 항목See Also

FOR XML에서 RAW 모드 사용Use RAW Mode with FOR XML