계산 열을 사용하여 자주 사용되는 XML 값 승격

아티클
08/21/2023

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

쿼리가 주로 몇 가지 요소 및 특성 값에 대해 만들어진 경우 이러한 수량을 관계형 열로 승격할 수 있습니다. 이는 전체 XML 인스턴스를 검색하는 동안 XML 데이터의 작은 부분에서 쿼리가 실행될 때 유용합니다. XML 열에 XML 인덱스 만들기는 필요하지 않습니다. 대신 승격된 열을 인덱싱할 수 있습니다. 승격된 열을 사용하도록 쿼리를 작성해야 합니다. 즉, 쿼리 최적화 프로그램은 XML 열의 쿼리를 승격된 열로 다시 대상으로 지정하지 않습니다.

승격된 열은 같은 테이블에 있는 계산 열이거나 테이블에서 사용자가 유지 관리하는 별개의 열일 수 있습니다. 이는 단일 값이 각 XML 인스턴스에서 승격되는 경우에 충분합니다. 그러나 다중값 속성의 경우 다음 섹션에 설명된 대로 속성에 대해 별도의 테이블을 만들어야 합니다.

xml 데이터 형식을 기반으로 하는 계산 열

xml 데이터 형식 메서드를 호출하는 사용자 정의 함수를 사용하여 계산 열을 만들 수 있습니다. 계산 열의 형식은 XML을 비롯한 모든 SQL 형식일 수 있습니다. 다음 예에서 확인할 수 있습니다.

예: xml 데이터 형식 메서드를 기반으로 하는 계산 열

책 ISBN 번호에 대한 사용자 정의 함수를 만듭니다.

CREATE FUNCTION udf_get_book_ISBN (@xData xml)
RETURNS varchar(20)
BEGIN
   DECLARE @ISBN   varchar(20)
   SELECT @ISBN = @xData.value('/book[1]/@ISBN', 'varchar(20)')
   RETURN @ISBN
END;

ISBN의 테이블에 계산 열을 추가합니다.

ALTER TABLE      T
ADD   ISBN AS dbo.udf_get_book_ISBN(xCol);

계산 열은 일반적인 방식으로 인덱싱될 수 있습니다.

예: xml 데이터 형식 메서드를 기반으로 계산된 열에 대한 쿼리

ISBN이 0-7356-1588-2인 ISBN을 얻으려면 다음을 <book> 수행합니다.

SELECT xCol
FROM   T
WHERE  xCol.exist('/book/@ISBN[. = "0-7356-1588-2"]') = 1;

다음과 같이 계산 열을 사용하도록 XML 열의 쿼리를 다시 작성할 수 있습니다.

SELECT xCol
FROM   T
WHERE  ISBN = '0-7356-1588-2';

사용자 정의 함수를 사용하여 xml 데이터 형식 및 계산 열을 반환하는 사용자 정의 함수를 만들 수 있습니다. 그러나 계산된 XML 열에는 XML 인덱을 만들 수 없습니다.

속성 테이블 만들기

XML 데이터의 일부 다중값 속성을 하나 이상의 테이블로 승격하고, 해당 테이블에 인덱스를 만들고, 쿼리를 다시 대상으로 지정하여 사용할 수 있습니다. 일반적인 시나리오는 몇 가지 속성이 대부분의 쿼리 워크로드를 포함하는 시나리오입니다. 다음을 수행할 수 있습니다.

다중값 속성을 저장할 테이블을 하나 이상 만듭니다. 테이블마다 하나의 속성을 저장하고 기본 테이블로 다시 조인하기 위해 속성 테이블에 있는 기본 테이블의 기본 키를 중복시키는 것이 편리할 수 있습니다.
속성의 상대적 순서를 유지하려면 상대적 순서에 대해 개별 열을 사용해야 합니다.
XML 열에 트리거를 만들어 속성 테이블을 유지 관리합니다. 트리거 내에서 다음 중 하나를 수행합니다.
- xml 데이터 형식 메서드(예: nodes() 및 value())를 사용하여 속성 테이블의 행을 삽입하고 삭제합니다.
- CLR(공용 언어 런타임)에서 스트리밍 테이블 반환 함수를 만들어 속성 테이블의 행을 삽입하고 삭제합니다.
- 기본 키를 사용하여 테이블 간의 조인을 사용하여 속성 테이블에 대한 SQL 액세스 및 기본 테이블의 XML 열에 대한 XML 액세스에 대한 쿼리를 작성합니다.

예: 속성 테이블 만들기

그림의 경우 작성자의 이름을 승격하려는 경우를 가정합니다. 책에는 하나 이상의 저자가 있으므로 이름은 다중값 속성입니다. 각 이름은 속성 테이블의 별도 행에 저장됩니다. 기본 테이블의 기본 키는 역 조인을 위해 속성 테이블에 중복됩니다.

create table tblPropAuthor (propPK int, propAuthor varchar(max));

예: XML 인스턴스에서 행 집합을 생성하는 사용자 정의 함수 만들기

다음 테이블 반환 함수 udf_XML2Table은 기본 키 값과 XML 인스턴스를 허용합니다. 요소의 모든 작성 <book> 자의 이름을 검색하고 기본 키, 이름 쌍의 행 집합을 반환합니다.

create function udf_XML2Table (@pk int, @xCol xml)
returns @ret_Table table (propPK int, propAuthor varchar(max))
with schemabinding
as
begin
      insert into @ret_Table
      select @pk, nref.value('.', 'varchar(max)')
      from   @xCol.nodes('/book/author/first-name') R(nref)
      return
end;

예: 속성 테이블을 채우는 트리거 만들기

삽입 트리거는 속성 테이블에 행을 삽입합니다.

create trigger trg_docs_INS on T for insert
as
      declare @wantedXML xml
      declare @FK int
      select @wantedXML = xCol from inserted
      select @FK = PK from inserted

   insert into tblPropAuthor
   select * from dbo.udf_XML2Table(@FK, @wantedXML);

삭제 트리거는 삭제된 행의 기본 키 값에 따라 속성 테이블에서 행을 삭제합니다.

create trigger trg_docs_DEL on T for delete
as
   declare @FK int
   select @FK = PK from deleted
   delete tblPropAuthor where propPK = @FK;

업데이트 트리거는 업데이트된 XML 인스턴스에 해당하는 속성 테이블의 기존 행을 삭제하고 속성 테이블에 새 행을 삽입합니다.

create trigger trg_docs_UPD
on T
for update
as
if update(xCol) or update(pk)
begin
      declare @FK int
      declare @wantedXML xml
      select @FK = PK from deleted
      delete tblPropAuthor where propPK = @FK

   select @wantedXML = xCol from inserted
   select @FK = pk from inserted

   insert into tblPropAuthor
      select * from dbo.udf_XML2Table(@FK, @wantedXML)
end;

예: 작성자의 이름이 같은 XML 인스턴스 찾기

쿼리는 XML 열에 형성될 수 있습니다. 또는 속성 테이블에서 이름 "David"를 검색하고 기본 테이블과 백 조인을 수행하여 XML 인스턴스를 반환할 수 있습니다. 예:

SELECT xCol
FROM     T JOIN tblPropAuthor ON T.pk = tblPropAuthor.propPK
WHERE    tblPropAuthor.propAuthor = 'David';

예제: CLR 스트리밍 테이블 반환 함수를 사용하는 솔루션

이 솔루션은 다음 단계로 구성됩니다.

XML 인스턴스에 경로 식을 적용하여 ISqlReader를 구현하고 스트리밍 테이블 반환 출력을 생성하는 CLR 클래스 SqlReaderBase를 정의합니다.
어셈블리 및 Transact-SQL 사용자 정의 함수를 만들어 CLR 클래스를 시작합니다.
사용자 정의 함수를 사용하여 속성 테이블을 유지 관리하여 삽입, 업데이트 및 삭제 트리거를 정의합니다.

이렇게 하려면 먼저 스트리밍 CLR 함수를 만듭니다. xml 데이터 형식은 ADO.NET 관리되는 클래스 SqlXml로 노출되며 XmlReader를 반환하는 CreateReader() 메서드를 지원합니다.

참고 항목

이 섹션의 예제 코드에서는 XPathDocument 및 XPathNavigator가 사용됩니다. 이렇게 하면 모든 XML 문서를 메모리에 로드할 수 있습니다. 일부 큰 XML 문서를 처리하기 위해 애플리케이션에서 비슷한 코드를 사용하는 경우 이 코드는 확장할 수 없습니다. 대신 메모리 할당을 작게 유지하고 가능하면 스트리밍 인터페이스를 사용합니다. 성능에 대한 자세한 내용은 CLR 통합 아키텍처를 참조 하세요.

public class c_streaming_xml_tvf {
   public static ISqlReader streaming_xml_tvf
(SqlXml xmlDoc, string pathExpression) {
      return (new TestSqlReaderBase (xmlDoc, pathExpression));
   }
}

// Class that implements ISqlReader
public class TestSqlReaderBase : ISqlReader {
XPathNodeIterator m_iterator;
   public SqlChars FirstName;
// Metadata for current resultset
private SqlMetaData[] m_rgSqlMetaData;

   public TestSqlReaderBase (SqlXml xmlDoc, string pathExpression) {
      // Variables for XPath navigation
      XPathDocument xDoc;
      XPathNavigator xNav;
      XPathExpression xPath;

      // Set sql metadata
      m_rgSqlMetaData = new SqlMetaData[1];
      m_rgSqlMetaData[0] = new SqlMetaData ("FirstName",
SqlDbType.NVarChar, 50);

      //Set up the Navigator
      if (!xmlDoc.IsNull)
          xDoc = new XPathDocument (xmlDoc.CreateReader());
      else
          xDoc = new XPathDocument ();
      xNav = xDoc.CreateNavigator();
      xPath = xNav.Compile (pathExpression);
      m_iterator = xNav.Select(xPath);
   }
   public bool Read() {
      bool moreRows = true;
      if (moreRows = m_iterator.MoveNext())
         FirstName = new SqlChars (m_iterator.Current.Value);
      return moreRows;
   }
}

그런 다음 CLR 함수에 해당하는 SQL_streaming_xml_tvf(표시되지 않음) streaming_xml_tvf Transact-SQL 사용자 정의 함수와 어셈블리를 만듭니다. 행 집합 생성을 위해 테이블 반환 함수인 CLR_udf_XML2Table을 정의하는 데 사용자 정의 함수가 사용됩니다.

create function CLR_udf_XML2Table (@pk int, @xCol xml)
returns @ret_Table table (FK int, FirstName varchar(max))
with schemabinding
as
begin
      insert into @ret_Table
   select @pk, FirstName
   FROM   SQL_streaming_xml_tvf (@xCol, '/book/author/first-name')
      return
end;

마지막으로 "속성 테이블을 채우기 위한 트리거 만들기" 예에서 표시된 것과 같이 트리거를 정의하고 udf_XML2Table을 CLR_udf_XML2Table 함수로 바꿉니다. 다음 예에는 삽입 트리거가 표시됩니다.

create trigger CLR_trg_docs_INS on T for insert
as
   declare @wantedXML xml
   declare @FK int
   select @wantedXML = xCol from inserted
   select @FK = PK from inserted

   insert into tblPropAuthor
      select *
   from    dbo.CLR_udf_XML2Table(@FK, @wantedXML);

삭제 트리거는 비-CLR 버전과 동일합니다. 그러나 업데이트 트리거는 함수 udf_XML2Table()를 CLR_udf_XML2Table()로 바꿉니다.

참고 항목

계산 열에서 XML 사용

계산 열을 사용하여 자주 사용되는 XML 값 승격

xml 데이터 형식을 기반으로 하는 계산 열

예: xml 데이터 형식 메서드를 기반으로 하는 계산 열

예: xml 데이터 형식 메서드를 기반으로 계산된 열에 대한 쿼리

속성 테이블 만들기

예: 속성 테이블 만들기

예: XML 인스턴스에서 행 집합을 생성하는 사용자 정의 함수 만들기

예: 속성 테이블을 채우는 트리거 만들기

예: 작성자의 이름이 같은 XML 인스턴스 찾기

예제: CLR 스트리밍 테이블 반환 함수를 사용하는 솔루션

참고 항목

피드백

피드백

추가 리소스