컴퓨토피아

부분적 XML 업데이트

XML 데이터 형식의 인플레이스 업데이트는 세분화된 데이터 수정 동안 상당한 성능 향상을 제공합니다. 새 상태(데이터 수정 후) 및 이전 상태(데이터 수정 전) 간의 차이점이 처리되고 XML 열 저장소를 비롯하여 주 XML 인덱스에 적용됩니다. 또한 주 XML 인덱스의 변경 사항이 보조 XML 인덱스에도 전파됩니다. 이렇게 하면 저장소에서 업데이트되는 데이터의 양이 더 적고 그에 따라 트랜잭션 로그가 절감되므로 성능 혜택을 얻을 수 있습니다. 대부분의 경우 이러한 절감은 새 상태 및 이전 상태를 비교하는 비용을 상쇄합니다.

이 경우에 가장 적합한 시나리오는 XML DML에서 명령문의 "값 대체"를 이용한 특성 또는 요소 값의 수정입니다. 이렇게 하려면 XML 열에 있는 각각의 주 XML 인덱스 및 보조 XML 인덱스에서 행을 하나씩 업데이트해야 합니다. 또한 이 업데이트는 업데이트된 특성 또는 요소를 포함하는 XML blob의 디스크에 있는 페이지에 로컬입니다. 물론 이전 값을 큰 값으로 대체하면 새 디스크 페이지가 기록됩니다. 다음은 업데이트가 매우 효율적인 경우의 예제입니다.

예제: 특성 값 업데이트

이 예제에 나타난 대로 <book> 의 <price> 수정은 XML 인스턴스 및 XML 인덱스의 인플레이스 업데이트를 수행합니다.

UPDATE docs
SET xCol.modify ('replace value of (/book/price/text())[1] with 29.99')

특성, 요소 또는 하위 트리 삽입의 경우, 새로 삽입된 노드 및 이 노드를 따르는 형제 항목이 해당 하위 트리와 함께 업데이트되거나 삽입됩니다. XML blob에서도 이와 비슷한 변경이 발생합니다. 노드 삭제의 경우에도 이와 유사하며 삭제 지점 위의 형제 항목이 업데이트됩니다.

최악의 시나리오는 노드를 XML 데이터 형식 인스턴스의 가장 왼쪽 조각으로서 삽입하거나 루트 요소의 가장 왼쪽 자식(child)을 삽입하는 동안 발생합니다. 이렇게 하면 전체 XML 인스턴스가 업데이트됩니다. 이 상황은 노드를 XML 인스턴스의 가장 오른쪽 조각 또는 루트 요소의 가장 오른쪽 자식(child)으로 삽입함으로써 피할 수 있습니다.

루트 요소의 가장 왼쪽 조각 또는 가장 왼쪽 자식(child)의 삭제에도 이와 비슷한 비용이 듭니다. 요소가 자주 삽입되고 삭제되는 경우에는 요소를 가장 오른쪽 조각 또는 루트 요소의 가장 오른쪽 자식(child)으로 삽입하는 편이 더 낫습니다. 다음 예제는 비용이 많이 드는 경우를 보여줍니다.

예제: 비용이 많이 드는 업데이트

<publisher> 요소가 <book> 요소의 가장 왼쪽 자식(child)으로 삽입되어 <book> 의 모든 하위 요소가 업데이트됩니다.

UPDATE docs
SET xCol.modify ('
insert <publisher>Microsoft Press</publisher>
before (/book/title)[1]')

<publisher> 를 <book> 의 가장 오른쪽 자식(child)으로 삽입하면 더 효율적입니다.

UPDATE docs
SET xCol.modify ('
insert <publisher>Microsoft Press</publisher> into (/book)[1]')

XML 스키마 제약 조건은 삽입 지점을 결정할 수 있고 새 노드를 가장 오른쪽의 가능한 위치에 삽입하면 최상의 성능을 얻을 수 있습니다.

형식의 결합으로 인한 최적화 저해

암시적 캐스트를 요구하는 union 형식의 값은, 보조 XML 인덱스가 경로를 일치시키는 데 사용될 수 있는 경우에도 값에 대한 보조 XML 인덱스의 조회를 막습니다. 이와 같이 union 형식의 값은 범위 스캔이 VALUE 보조 XML 인덱스에서 발생하지 못하게 합니다. 자세한 내용은 이 문서의 "범위 제약 조건"을 참조하십시오. 같은 추론이 <xs:anyAttribute>에도 적용됩니다.

모델 그룹(<xs:choice> 및 <xs:all> ), 대체 그룹 및 와일드카드 섹션(xs:any)은 컨텐츠 모델로서 형식의 결합을 갖습니다. 정확한 형식이 쿼리 컴파일 및 최적화 동안 알려지지 않는 경우 런타임 형식 캐스트가 그 값에 대해 작업하기 위해 필요할 수 있습니다. 이렇게 하면 쿼리 속도가 느려집니다. 따라서 성능상의 이유로 가능한 경우 이러한 XML 스키마 구조 및 데이터 형식을 피해야 합니다.

요소의 단일 발생을 나타내는 스키마 구조를 사용하면 쿼리 최적화에 도움이 됩니다. 이러한 이유로 <xs:choice> 구조가 선택적 요소가 있는 <sequence> 에 비해 선호됩니다.

XML 인덱스 선택 비활성화

XML 인덱스가 제약 조건을 평가하기 전 또는 후에 수정되도록 쿼리 최적화 프로그램이 보장하지 않기 때문에 XML 인덱스 선택은 check 제약 조건에서 비활성화됩니다. 이 문서의 성능 지침을 따라 제약 조건이 XML blob 상에서 효율적으로 평가될 수 있도록 하기 위해 충분한 주의를 기울여야 합니다. 뿐만 아니라 XML 인덱스 선택은 CHECK OPTION이 있는 뷰에서 비활성화됩니다.

XML 열의 전체 텍스트 인덱스

XML 열의 XML 인덱스와는 독립적으로 이 열에 전체 텍스트 인덱스를 만들 수 있습니다. 전체 텍스트 인덱스는 요소 콘텐츠를 인덱싱하고 XML 태그 및 특성 값을 무시하며 태그를 토큰 경계로 사용합니다.

XQuery 함수 fn:contains()가 리터럴, 부분 문자열 일치의 의미론을 갖는 반면 CONTAINS()를 사용하는 전체 텍스트 검색은 형태소 분석을 이용한 토큰 일치를 사용합니다. 따라서 이들의 의미론은 상이합니다. 차이점을 예로 들어 보자면, 단어 "data"를 검색하면 XQuery에서는 단어 "database"와 일치하지만 전체 텍스트 의미론에서는 일치하지 반면, 단어 "drove"를 검색하면 전체 텍스트 의미론에서는 단어 "driving"과 일치하지만 XQuery에서는 일치하지 않습니다. 게다가 전체 텍스트 검색은 특성 값 검색에 사용할 수 없고, 한편 XQuery 식은 혼합 콘텐츠를 검색하기 위해 집계 함수 fn:string()을 사용해야 합니다.

전체 텍스트 인덱스가 XML 열에 존재하는 경우 다음 작업을 수행하는 것이 좋습니다.

예제: XQuery 일치와 전체 텍스트 결합

다음 쿼리는 키워드 'data'에 대한 전체 텍스트 검색을 수행하고 단어 "data"가 <book>의 <title>요소의 컨텍스트에서 나타나는지 확인합니다. 이 쿼리는 전체 텍스트 contains() 메서드를 사용하여 검색 단어를 포함하고 있는 XML 인스턴스를 찾습니다. 즉, XML 데이터 형식 메서드 exist()는 XML 인스턴스가 올바른 컨텍스트에서 부분 문자열을 포함하는지 확인합니다.

select *
from docs
where contains(xCol, 'data')
AND xCol.exist('/book/title/text()[contains(.,"data")]') = 1

예제: 전체 텍스트에서 접두사 검색 사용

전체 텍스트 인덱스에서 접두사 검색을 수행할 수 있습니다. "database"와 같은 "data"로 시작하는 모든 키워드와 일치하는 쿼리에 대해, 위 쿼리는 다음과 같이 다시 작성될 수 있습니다. XQuery 검색은 "database"와도 일치합니다.

select *
from docs
where contains(xCol, '"data*"')
and xCol.exist('/book/title/text()[contains(.,"data")]') = 1

전체 텍스트 contains() 메서드에서 큰따옴표의 사용에 주의하십시오.

스냅샷 격리 및 XML 인덱스

XML 데이터 수정은 이전 XML 인스턴스를 새 XML 인스턴스로 업데이트합니다. 이러한 변경은 주 XML 인덱스 및 보조 XML 인덱스에 전파됩니다. 기본 테이블 및 XML 인덱스에서 수정된 행은 잠겨지고 행 및 페이지 잠금이 쿼리 분석기의 결정에 따라 테이블 잠금으로 에스컬레이션될 수 있습니다. 특히 작업 부하에서 수정이 잦은 경우에는 잠금 에스컬레이션으로 인해 동시성이 나빠집니다.

SQL Server 2005에서 스냅샷 기반 격리는 "스냅샷"이라는 새로운 격리 수준과 새로운 read-committed 격리 수준의 구현을 도입했습니다. 이에 대한 자세한 내용은 SQL Server 온라인 설명서에서 찾아볼 수 있습니다. 이들은 데이터베이스를 스냅샷 격리에 사용할 수 있는 경우 판독기와 작성기 사이의 잠금 경합을 제거하는 내부 버전 관리 메커니즘에 기반합니다. 잠금 경합이 감소되면 처리량이 더 높아질 수 있습니다.

스냅샷 기반 격리 하에서 읽기 작업은 동시 업데이트에서 차단되지 않고서 버전 관리되는 데이터에 액세스할 수 있습니다. 이렇게 차단이 감소되면 동시 작업 부하에서 트랜잭션 처리량이 잠재적으로 향상됩니다.

스냅샷 격리를 사용하면 업데이트 시 XML 열 값과 해당 주/보조 XML 인덱스 행이 버전 관리됩니다. 이렇게 하면, XML이 아닌 열의 수정으로 인해 포함하는 행이 버전 관리되는 경우 XML 열의 불필요한 버전 관리를 피할 수 있습니다. 이 최적화는 XML 처리에 있어 스냅샷 격리를 매우 유용하게 만듭니다.

쿼리 및 데이터 수정

인덱스된 XML에 대한 여러 value() 메서드 실행 병합

인덱스된 경우에 더 빠른 실행을 위해, SELECT 목록의 동일한 형식화된 XML 열에서 여러 value() 메서드의 실행을 결합할 수 있습니다. 실행을 결합할지 여부는 쿼리 분석기가 쿼리 비용을 기반으로 결정합니다. 이로 인해 속도가 현저히 향상됩니다. 아래에 예제가 나와 있습니다.

예제: 여러 value() 메서드 실행 결합

XML 스키마 모음 bookCollection에 있는 XML 스키마 이름 공간 "http://www.microsoft.com/book"에 의해 요소의 콘텐츠 모델이 정의된다고 가정합시다. 또한 이 예제에서 XML 열 xDoc이 docs 테이블에 추가되고 bookCollection을 사용하여 형식화된다고 가정합시다. XML 스키마 정의는 아래와 같습니다.

CREATE XML SCHEMA COLLECTION bookCollection AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.microsoft.com/book"
targetNamespace="http://www.microsoft.com/book">
<xsd:element name="book" type="bookType" />
<xsd:complexType name="bookType">
<xsd:sequence>
<xsd:element name="title" type="xsd:string" />
<xsd:element name="author" type="authorName"
maxOccurs="unbounded"/>
<xsd:element name="price" type="xsd:decimal" />
</xsd:sequence>
<xsd:attribute name="subject" type="xsd:string" />
<xsd:attribute name="releasedate" type="xsd:integer" />
<xsd:attribute name="ISBN" type="xsd:string" />
</xsd:complexType>
<xsd:complexType name="authorName">
<xsd:sequence>
<xsd:element name="first-name" type="xsd:string" />
<xsd:element name="last-name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>'
GO

ALTER TABLE docs ADD COLUMN xDoc XML (DOCUMENT bookCollection)
GO

아래 쿼리에서는, value() 메서드가 동일한 XML 열에서 호출되고 <title> 및 <price> 요소의 단일 카디널리티가 XML 스키마에서 정적으로 추론되기 때문에 value() 메서드의 실행을 결합합니다.

WITH XMLNAMESPACES ('http://www.microsoft.com/book' AS "bk")
SELECT xCol.value ('/bk:book/bk:title', 'nvarchar(128)') Title,
xCol. value('/bk:book/bk:price', 'decimal(5,2)') Price
FROM docs

최적화가 발생하려면 다음 조건이 유지되어야 합니다.

존재 확인을 위해 exist() 메서드 사용

보다 나은 성능을 위해 가능한 경우 XML 데이터 형식의 value() 메서드 대신 exist() 메서드를 사용하십시오. exist() 메서드는 SQL WHERE 절에서 사용하는 경우 가장 유용하며 value() 메서드보다 XML 인덱스를 더 효과적으로 활용합니다. XQuery 식에서 sql:variable() 및 sql:column()을 사용하는 경우에도 마찬가지입니다.

예를 들어, exist() 메서드를 사용하여 "Writing Secure Code"라는 제목의 책을 검색하는 다음 쿼리를 살펴봅시다.

SELECT *
FROM docs
WHERE xCol.exist('(/book/title/text())[.="Writing Secure Code"]') = 1

PATH 또는 VALUE 보조 XML 인덱스가 이들 인덱스에서의 값 조회(이 예제에서 "Writing Secure Code")를 포함하여 경로 식((/book/title/text())[.="Writing Secure Code"])을 평가하여 반환할 XML 인스턴스를 산출하기 위해 사용됩니다. 경로 및 검색 값의 선택성이 높은 경우 결과 실행이 열의 모든 XML blob에 대해 경로 식을 평가하는 것보다 훨씬 더 빠를 수 있습니다. 검색 값은 sql:variable() 또는 sql:column()을 사용하여 제공할 수 있습니다. 자세한 내용은 이 문서의 "XQuery 및 XML DML 식 매개 변수화"를 참조하십시오.

아래와 같이 value() 메서드를 사용하여 작성한 쿼리는

SELECT *
FROM docs
WHERE xCol.value('(/book/title)[1]', 'varchar(50)') = 'Writing Secure Code'

먼저 모든 책 제목을 평가한 다음 필터 "Writing Secure Code"를 적용합니다. 이렇게 하면 XML 인덱스 조회에서 필터 값 "Writing Secure Code"가 사용되지 않기 때문에 쿼리 실행의 효율성이 떨어집니다. SQL 변수 또는 또 다른 value() 메서드를 사용하여 지정한 필터 값도 유사한 동작(behavior)을 나타냅니다.

nodes()-value() 결합 사용
nodes() 메서드는 value() 메서드 내에서 사용할 수 있는 내부 노드 참조의 행 집합을 생성하여 이들 노드에서 스칼라 값을 추출합니다. 이들 메서드를 함께 사용하여 XML 데이터를 관계형 형식으로 표시할 수 있습니다.

아래 예제에서 보는 대로, value() 메서드에서 컨텍스트 노드의 특성을 선택하는 데 사용되는 서수 술어가 제거될 수 있도록 nodes() 메서드의 출력에서 각 행은 단일 참조를 표시합니다. 또한 nodes() 메서드가 정확히 하나의 참조를 제공할 경우 nodes() 메서드를 완전히 제거하면 쿼리가 더 빠르게 수행됩니다. 이러한 최적화는 XML 변수 및 매개 변수에 가장 유용합니다.

예제: nodes() 메서드로 서수 술어 제거

이 쿼리는 docs테이블의 xCol 열에 있는 각 책의 인스턴스에서 ISBN 특성을 추출합니다. nodes() 메서드는 각 개별 <book> 요소(컨텍스트 노드)에 대한 별도의 참조를 내보내고 컨텍스트 노드에는 최대 하나의 @ISBN 특성이 있을 수 있습니다.

SELECT ref.value('@ISBN', 'nvarchar(32)')
FROM docs CROSS APPLY xCol.nodes('/book') AS node(ref)

각 XML 인스턴스에서 요소가 하나만 발생할 경우 다음의 다시 작성된 쿼리가 더 빠릅니다.

SELECT xCol.value('(/book/@ISBN)[1]', 'nvarchar(32)')
FROM docs

XML blob을 위한 최적화

XML 변수 및 매개 변수의 더 나은 확장성을 위한 여러 개의 tempDB 파일

XML 변수 및 매개 변수는 그 값이 작은 경우에는 주 메모리를 저장소로 사용합니다. 단, 큰 값은 tempdb 저장소에서 백업합니다. 다중 사용자 시나리오에서 많은 수의 대형 XML blob이 발생할 경우 tempdb 경합이 충분한 확장성을 위해 병목 상태를 일으킬 수 있습니다. 여러 개의 tempdb 파일을 만들면 저장소 경합이 감소되고 확장성이 훨씬 더 좋아집니다. 다음 예제는 여러 개의 tempdb 파일을 만들 수 있는 방법을 보여줍니다.

예제: 여러 개의 tempdb 파일 만들기

이 예제에서는 각각 처음 크기가 8MB인 두 개의 tempdb용 추가 데이터 파일과 각각 처음 크기가 1MB인 두 개의 로그 파일을 만듭니다.

USE TEMPDB
GO

ALTER DATABASE tempdb ADD FILE
(NAME = 'Tempdb_Data1',
FILENAME = 'C:\temp\Tempdb_Data1.MDF', SIZE = 8 MB),
(NAME = 'Tempdb_Data2',
FILENAME = 'C:\temp\Tempdb_Data2.MDF', SIZE = 8 MB)
GO

ALTER DATABASE tempdb ADD log FILE
(NAME = 'Tempdb_Log1',
FILENAME = 'C:\temp\Tempdb_Log1.LDF', SIZE = 1 MB),
(NAME = 'Tempdb_Log2',
FILENAME = 'C:\temp\Tempdb_Log2.LDF', SIZE = 1 MB)
GO

이 파일은 ALTER DATABASE tempdb REMOVE FILE 명령을 사용하여 제거될 수 있습니다. 자세한 내용은 SQL Server 2005용 SQL Server 온라인 설명서를 참조하십시오.

XML 데이터 형식으로의 추가 캐스트 제거

XML 형식의 입력 인수를 가진 inlinable 함수에서 호출자는 XML 데이터 형식으로 암시적으로 변환되는 텍스트 또는 이진 값을 제공할 수 있습니다. 호출 수신자의 본문에서 XML 인수의 각 사용은 입력 값을 XML 데이터 형식으로 캐스트합니다. 이 비용은 인수를 XML 데이터 형식 변수(XML 데이터 형식으로 한 번의 인수 값 변환 유발)로 복사함으로써 피할 수 있고 XML 변수는 함수 또는 저장 프로시저의 본문에서 여러 번 사용할 수 있습니다. 다음 예제는 이러한 특징을 예시합니다.

예제: 변환 제거

책의 제목 및 ISBN을 반환하는 아래 GetTitleAndIsbnOfBook() 함수를 살펴봅시다.

CREATE FUNCTION GetTitleAndIsbnOfBook (@book XML)
RETURNS TABLE AS
RETURN
SELECT @book.value ('(/book/@ISBN)[1]', 'nvarchar(32)') ISBN,
@book.value ('(/book/title)[1]', 'nvarchar(128)') title

함수가 문자열 값으로 호출되면 각 value() 메서드 호출에 대해 XML 데이터 형식으로의 변환이 발생합니다. 함수는 XML 데이터 형식으로의 인수 변환이 한 번만 발생하도록 다음과 같이 다시 작성될 수 있습니다. 하지만 다중 명령문 테이블 반환 함수에 필요한 테이블 변수 @retTab으로 인해 추가 비용이 발생하는데, 이 비용은 XML 데이터의 크기가 큰 경우 XML 변수에 대한 충분한 횟수의 액세스로 상쇄될 수 있습니다.

CREATE FUNCTION GetTitleAndIsbnOfBookOpt (@book varbinary(max))
RETURNS @retTab TABLE (ISBN nvarchar(32), title nvarchar(128)) AS
BEGIN
DECLARE @xbook XML
SET @xbook = @book
INSERT INTO @retTab
SELECT @xbook.value ('(/book/@ISBN)[1]', 'nvarchar(32)'),
@xbook.value ('(/book/title)[1]', 'nvarchar(128)')
RETURN
END

단일 요소 지정

단일 카디널리티 평가는 쿼리 및 데이터 수정 문에서 서수를 지정할 필요성을 제거합니다. 이로 인해 쿼리 계획이 간소화되고 효율적인 JOIN 작업이 생성됩니다. 일반적으로 단일 카디널리티 평가에는 중첩 루프 조인에서 내부 및 외부 루프에 대한 적절한 선택이 수반됩니다.

형식화된 XML에서 기본적으로 요소는 minOccurs 및 maxOccurs의 값을 사용하여 재정의되지 않는다면 XML 스키마 구조의 단일 카디널리티를 갖습니다. 또한 형식화된 XML 열, 변수 및 매개 변수의 DOCUMENT 제약 조건은 XML 데이터 형식 인스턴스에서 정확히 하나의 최상위 요소를 보장합니다.

형식화되지 않은 데이터의 경우 또는 스키마에 여러 형제 요소가 허용되는 경우, 아래 예제에 나타난 대로 경로 식을 만족시키는 노드를 정확하게 하나만 선택하는 서수 값을 사용하여 노드의 단일 카디널리티가 경로 식에 표시될 수 있습니다. 서수 [1]은 Transact -SQL TOP 1 오름차순을 사용하여 평가되는 반면 서수 last()는 TOP 1 내림차순으로 평가됩니다. 또한 nodes() 메서드는 결과 XML 인스턴스 각각에 대해 단일 컨텍스트 항목을 설정합니다.

단일 노드의 선택이 생략된 경우 쿼리 최적화 프로그램이 지나치게 높을 수 있는 기본 카디널리티 평가를 사용합니다. 예를 들면, 이는 술어를 처리해야 하는 경우 중첩 루프 조인에서 내부 및 외부 루프에 대해 차선책을 선택하게끔 유발할 수 있습니다. 그 효과는 XML 인덱스가 존재하지 않고 보다 효율적인 카디널리티 평가를 위해 사용할 수 있는 통계 정보가 없는 XML blob의 경우에 더욱 크게 나타납니다.

예제: 형식화되지 않은 XML의 단일 카디널리티 지정

xCol 열의 각 XML 인스턴스가 <title> 하위 요소를 하나만 갖는 최상위 요소를 하나만 포함한다고 가정합시다. 다음 쿼리를 살펴봅시다.

SELECT xCol.query ('/book/title')
FROM docs

쿼리 최적화 프로그램은 <title> 요소에 기본 카디널리티 평가를 사용합니다. 각 <book>은 <title>이 단일 요소가 되도록 하나의 제목을 갖지만 최적화 프로그램의 평가는 그에 비해 훨씬 더 높습니다. 다시 공식화한 다음 쿼리는

SELECT xCol.query ('(/book/title)[1]')
FROM docs

올바른 카디널리티를 최적화 프로그램에 전달합니다. 비슷한 모양의 경로 식 (/a/b)[1]과 /a/b [1] 간의 의미론적 차이점이 MSDN 기사 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법에 설명되어 있습니다.

XML 데이터 형식 메서드의 다중 실행 제거

다음 쿼리는

SELECT case isnumeric (xCol.value ('(/book/price)[1]', 'nvarchar(32)'))
when 1 then xCol.value ('(/book/price)[1]', 'decimal(5,2)')
else 0
end
FROM docs

책의 <price>를 계산한 다음 가격이 숫자 형식인 경우 가격을 십진수(5, 2)로 변환합니다. 이 논리는 응용 프로그램에서 숫자가 아닌 가격 값이 발생할 수 있는 경우에 유용합니다.

SELECT case isnumeric(Price)
when 1 then CAST (Price AS decimal(5,2))
else 0
end
FROM (SELECT xCol.value ('(/book/price)[1]', 'nvarchar(32)') Price
FROM docs) T

동일한 최적화가 NULLIF()와 같은 다른 곳에서도 사용될 수 있습니다.

SELECT NULLIF (Title, '')
FROM (SELECT xCol.value ('(/book/title)[1]', 'nvarchar(64)') Title
FROM docs) T

NULLIF()에서 value() 메서드를 사용하면 이 메서드가 비어 있지 않은 문자열을 반환할 경우 value() 메서드가 두 번 처리됩니다.

Data(),text() 및 string() 접근자

XQuery는 노드에서 형식화된 원자 값을 추출하기 위한 fn:data() 함수, 텍스트 노드를 반환하기 위한 노드 테스트 text() 및 노드의 문자열 값을 반환하기 위한 fn:string() 함수를 제공합니다. 이들의 사용은 혼동될 수 있습니다. SQL Server 2005에서 이를 올바로 사용하기 위한 지침이 XML 인스턴스 <age>12</age>를 사용하여 아래에 예시되어 있습니다.

형식화되지 않은 XML에서 텍스트 집계

XQuery 의미론에 따라, 형식화되지 않은 XML에서 다음과 같은 두 쿼리는 술어를 평가하기 위해 <title> 요소 아래의 모든 텍스트 노드를 집계해야 합니다. 이 때문에 검색 문자열에 대한 XML 인덱스 조회가 억제됩니다.

SELECT xCol.value ('(/book/title[.="Writing Secure Code"])[1]',
'nvarchar(64)')
FROM docs

또는

SELECT xCol.value ('(/book/title
[fn:string()="Writing Secure Code"])[1]'), 'nvarchar(64)')
FROM docs

<title> 요소에 텍스트 노드가 하나만 있는 경우 더 효율적인 쿼리 작성 방법은 아래와 같이 텍스트 노드의 술어를 평가하는 방법입니다.

SELECT xCol.value ('(/book/title/text())[1]
[. = "Writing Secure Code"]', 'nvarchar(64)')
FROM docs

이 경우에는 "Writing Secure Code" 값에 대한 XML 인덱스 조회가 발생합니다.

XQuery 및 XML DML 식 매개 변수화

XQuery 및 XML DML 식은 자동으로 매개 변수화되지 않습니다. 따라서 두 XQuery 식이 매개 변수의 값만 다른 경우, 동적 SQL 문을 사용하는 대신 sql:column() 또는 sql:variable()을 사용하여 XQuery 또는 XML DML 식에 매개 변수 값을 제공하는 것이 더 좋습니다. 이들 함수를 사용하면 쿼리가 자동으로 매개 변수화됩니다.

아래 예제는 저장 프로시저 실행을 보여줍니다. 이 기법은 쿼리, 함수/메서드 호출 또는 데이터 수정 문의 매개 변수화에 적용할 수 있습니다.

예를 들어, 아래 저장 프로시저는 입력 인수보다 낮은 가격의 책을 찾습니다.

CREATE PROC sp_myProc
@Price decimal
AS
SELECT *
FROM docs
WHERE 1 = xCol.exist('(/book/price)[. < sql:variable("@Price")]')

ADO.NET 및 OLEDB에서 @Price의 입력 값을 매개 변수에 바인딩합니다. 이렇게 하면 매개 변수가 서로 다른 값에 바인딩될 때 쿼리를 다시 컴파일할 필요가 없습니다. sql:column()을 사용하면 이와 비슷한 이점을 얻을 수 있습니다.

다음 Visual Basic.NET 코드는 저장 프로시저 호출에서 매개 변수 바인딩을 보여줍니다.

'myConn is the connection string
SqlCommand cmd = New SqlCommand("sp_myProc", myConn)
cmd.CommandType = CommandType.StoredProcedure

'Parameter binding
Dim myParm As SqlParameter = cmd.Parameters.Add("@Price", _
SqlDbType.Decimal)
myParm.Direction = ParameterDirection.Input
myParm.value = 2

'Invoke the stored procedure
SqlDataReader myReader = cmd.ExecuteReader()

'Invoke the stored procedure a second time
myParm.value = 49.99
SqlDataReader myReader = cmd.ExecuteReader()

자세한 내용은 Microsoft Visual Studio.NET 설명서 (영문)를 참조하십시오.

예제: 데이터 수정에서 sql:variable() 사용

ISBN이 "0-2016-3361-2"인 <book>의 <price>가 10% 할인되었다고 가정합시다. 할인과 ISBN 둘 다 XML 데이터 수정 문에 매개 변수로 전달될 수 있고 이 문은 다른 책 또는 다른 할인에 대해 똑같이 유지됩니다.

DECLARE @discountFactor decimal, @sqlisbn nvarchar(32)
SET @discountFactor = 0.9
SET @sqlisbn = N'0-7356-1588-2'

UPDATE docs
SET xCol.modify('replace value of (/book/price/text())[1] with
sql:variable("@discountFactor")*(/book/price/text())[1]')
WHERE xCol.exist('/book[@ISBN = sql:variable("@sqlisbn")]') = 1

예제 : 요소 구성에서 sql:variable() 사용

아래에 나와 있는 modify() 메서드는 구성된 요소 내에서 값을 제공하기 위한 sql:variable()의 사용을 보여줍니다.

DECLARE @name nvarchar(64)
SET @name = 'Microsoft Press'
UPDATE docs
SET xCol.modify ('
insert < publisher Name = "{sql:variable("@name")}"></publisher>
into (/book/title)[1]')

술어 및 서수의 최적화

노드 테스트 또는 분기가 없는(즉, 경로의 중간 노드에 술어 또는 서수가 없는) 전체 경로(즉, 루트 노드로부터 자식(child) 및 자신의 축만을 포함하는 선택한 노드에 이르는 절대 위치 경로)는 분기가 있는 경로 식보다 효율적으로 평가할 수 있습니다. 인덱스된 경우에 전체 경로는 인덱스 탐색에 사용될 수 있습니다. XML blob의 경우, 이러한 경로의 구문 분석은 분기 또는 와일드카드(*)가 있는 경로의 구문 분석보다 더 빠릅니다.

경로 끝부분의 노드 테스트 및 술어는 선택한 노드에서 필터로 사용됩니다. 인덱스가 사용됩니다. XML blob의 경우 구문 분석이 효율적입니다. 아래에 예제가 나와 있습니다.

예제: 전체 경로 평가

이름이 Davis인 저자가 쓴 책을 선택하는 경로 식을 생각해 봅시다.

SELECT xCol.query ('/book[author/first-name = "Davis"]')
FROM docs

술어가 <book> 요소에 직접 존재하지 않더라도 축소 경로 /book/author/first-name을 사용하여 위치한 <first-name> 노드는 값 "Davis"에 의해 필터링됩니다. 반환된 <book> 요소는 주어진 술어를 충족하는 것들입니다.

경로 기반 조회는 /book//first-name과 같이 술어 또는 서수가 없는 부분적으로 지정된 경로에도 효율적입니다. 쿼리 컴파일러는 XML 인덱스에서 이러한 경로를 일치시키기 위해 LIKE 연산자를 사용합니다. 그러므로 가능한 한 경로의 많은 부분을 지정하는 것이 보다 효율적인 처리에 도움이 됩니다.

/book[@ISBN = "1-8610-0157-6"]/author/first-name에서와 같이 분기(즉, 경로 식의 중간에 있는 노드 테스트 및 서술어)는 경로 식 /book[@ISBN = "1-8610-0157-6"] 및 /book/author/first-name을 평가하고 <book> 요소의 두 집합 사이의 교집합을 취합니다. 따라서 분기 없는 경로 식보다 실행이 느려집니다. 경로 식의 중간에 노드 테스트 및 술어를 사용하는 것을 가능한 한 피하는 것이 효과적입니다. 이는 “일반적 태그와 구체적 태그의 비교” 예제에 설명된 대로, 데이터 모델링 시 신중한 주의를 기울임으로써 가능한 경우가 종종 있습니다.

경로의 끝으로 서수 이동

정적 형식의 정확성을 위해 경로 식에 사용되는 서수는 경로 식의 끝부분에 넣을 수 있는 좋은 후보입니다. 경로 식 book[1]/title[1]은 (/book/title)[1]과 동일합니다. 후자는 문서 순서에서 <book> 요소 아래의 첫 번째 <title> 요소를 확인함으로써 XML 인덱스 및 XML blob 두 경우 모두에 대해 모두 더 빠르게 평가될 수 있습니다. 이와 유사하게 경로 식 (/book/@ISBN)[1]은 /book[1]/@ISBN보다 더 빠른 실행을 가져옵니다.

컨텍스트 노드를 사용한 술어 평가

술어, 서수 및 노드 테스트를 경로 식의 끝으로 이동하는 것 외에도 컨텍스트 노드를 사용하여 이러한 조건을 평가하면 한층 더 나은 성능을 얻을 수 있습니다. 아래에 이에 대한 쿼리 재작성 예제가 나와 있습니다.

예제: 컨텍스트 노드를 사용한 술어 평가

아래 쿼리는 “security”라는 제목의 책을 검색합니다. 이 쿼리에는 두 경로 식, /book 및 /book/@subject의 평가와 후자 경로에 대한 “security” 값 확인이 필요합니다.

SELECT *
FROM docs
WHERE xCol.exist ('/book[@subject = "security"]') = 1

다시 작성된 아래 쿼리는 단일 경로 /book/@subject를 평가하고 이 경로에 "security" 값이 있는지 확인합니다. 이렇게 하면 위 쿼리보다 쿼리 계획이 더 단순해지고 실행이 훨씬 더 빨라집니다.

SELECT *
FROM docs
WHERE xCol.exist ('/book/@subject[. = "security"]') = 1

범위 조건

범위 조건은 형식화된 XML의 사용을 통해 이점을 얻습니다. XML 열 및 XML 인덱스에 저장된 데이터는 XML 스키마에 지정된 형식 정의에 따라 형식화됩니다. 값 비교는 데이터의 런타임 변환을 피하고 VALUE 보조 XML 인덱스에 대한 범위 스캔을 허용합니다. 또한 이를 위해서는 다음 예제에 나타난 대로, 효율적인 액세스를 위해 범위 조건에 컨텍스트 노드(.)를 지정해야 합니다.

예제: 범위 조건의 컨텍스트 노드

$9.99와 $49.99 범위에 있는 가격대의 책을 찾는 쿼리를 고려해 봅시다.

SELECT xCol
FROM docs
WHERE xCol.exist ('/book[price > 9.99 and price < 49.99]') = 1

경로 식 /book/price > 9.99 및 /book/price < 49.99가 각각 따로 평가됩니다. <book> 요소 아래에 여러 <price> 요소가 존재할 수 있기 때문에 쿼리 최적화 프로그램은 <price> 요소가 동일하다는 사실을 알지 못합니다. 이 때문에 VALUE 보조 XML 인덱스에 대한 범위 스캔이 억제됩니다. 아래에 다시 작성된 쿼리는 <price>에 동일한 컨텍스트 노드를 사용하고 9.99와 49.99 사이의 값에 대해 VALUE 보조 XML 인덱스의 범위 스캔이 발생하도록 보장합니다. 이로 인해 성능이 더 좋아집니다.

SELECT xCol
FROM docs
WHERE xCol.exist ('/book/price[. > 9.99 and . < 49.99]') = 1

동적 쿼리

XQuery 식은 XML 데이터 형식 메서드 내에서 리터럴로 지정됩니다. 이들의 평가는 사용 가능한 경우 쿼리 최적화 프로그램에서 선택한 XML 인덱스를 사용합니다.

XQuery 식을 리터럴 대신 동적으로 지정할 수 있으면 응용 프로그램 개발이 편리합니다. 이는 다음 방식으로 가능합니다.

쿼리 구성
쿼리를 문자열로 작성하고 sp_executesql을 사용하여 실행합니다. EXEC와 달리, 이렇게 하면 컴파일된 쿼리 계획이 캐시되어 최적화 프로그램이 컴파일된 계획을 다시 사용할 수 있습니다. 쿼리는 문자열로 구성되므로 매개 변수화될 수 있고 포함된 매개 변수를 포함할 수 있습니다. SQL 주입 공격을 피하려면 충분한 주의를 기울여야 합니다.

XPath 함수 사용
XPath 식의 각 위치 단계를 name() 함수 또는 local-name() 및 namespace-URI() 함수로 대체합니다. 이렇게 하면 노드 이름 및 검색 값으로 전달할 수 있는 쿼리가 만들어집니다. "XQuery 또는 XML DML 식 매개 변수화" 예제에 설명된 대로 추가적으로 매개 변수화할 수 있습니다. 이와 같은 매개 변수화된 쿼리는 응용 프로그램 개발에 편리합니다. 하지만 컴파일 시에 구체적인 경로가 알려지지 않으므로, 이에 대해 생성된 쿼리 계획은 XML 인덱스를 무시합니다.

쿼리 구성 접근 방법은 경로 식 매개 변수화보다 더 능률적으로 작동하지만 런타임 쿼리 컴파일 비용이 들기 때문에, 전체 쿼리를 리터럴로 지정하는 것보다 더 느려집니다. 사용자가 전달한 실제 쿼리는 SQL 주입 공격을 피하기 위해 유효성이 검사되어야 합니다. 그렇지 않으면, 쿼리의 매개 변수화(이 문서의 “XQuery 및 XML DML 식 매개 변수화")를 위해 이 접근 방법을 사용하지 말아야 합니다. 다음 예제는 이 접근 방법을 예시합니다.

노드 이름을 사용하여 노드 테스트를 지정하는 두 번째 접근 방법은 SQL 주입 문제를 방지합니다. 하지만 쿼리 계획이 복잡해지고 원래 쿼리보다 훨씬 더 비능률적으로 수행됩니다. 이 접근 방법은 두 번째 예제에 나와 있습니다.

예제: sp_executesql를 사용한 쿼리

다음 쿼리를 동적으로 작성하여 매개 변수를 사용하여 @subject의 검색 값을 전달하기를 원한다고 가정합시다.

SELECT *
FROM docs
WHERE xCol.exist('/book[@subject = "security"]') = 1

동적 쿼리를 아래에 나타난 대로 작성하고 실행할 수 있습니다. 쿼리 문자열은 @SQLString 변수에 만들어지고 exist() 메서드에서 사용된 포함된 변수 @bksubj를 포함합니다. @subj 변수는 매개 변수의 런타임 값을 제공합니다. @SQLString을 사용하여 전달된 동적 쿼리는 SQL 주입 공격을 피하기 위해 유효성이 검사되어야 합니다(아래에는 표시되어 있지 않습니다).

DECLARE @SQLString NVARCHAR(500)
DECLARE @subj NVARCHAR(64)
DECLARE @ParmDefinition NVARCHAR(500)
--- Build the SQL string once
SET @SQLString =
N'SELECT *
FROM docs
WHERE xCol.exist(''/book[@subject=sql:variable("@bksubj")]'')=1'
SET @ParmDefinition = N'@bksubj NVARCHAR(64)'
--- Execute the string with the first parameter value
SET @subj = 'security'
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@bksubj = @subj

예제: local-name()을 사용한 쿼리

위 쿼리를 다음과 같이 태그 이름을 리터럴로 사용하도록 다시 작성할 수 있습니다.

DECLARE @elemName nvarchar(4000), @attrName nvarchar(4000)
DECLARE @subjValue nvarchar(4000)
SET @elemName = N'book'
SET @attrName = N'subject'
SET @subjValue = N'security'
SELECT *
FROM docs
WHERE xCol.exist('/*[local-name() = sql:variable("@elemName") and
@*[local-name() = sql:variable("@attrName") and
. = sql:variable("@subjValue")]]') = 1

다시 작성된 쿼리는 와일드카드(*)와 노드 이름을 사용하는 노드 테스트를 포함하므로 효과적으로 최적화하기가 어렵습니다. 결과적으로 원래 쿼리 및 쿼리 구성 접근 방법에 비해 훨씬 더 비능률적으로 수행됩니다.

XML 데이터에서 행 집합 생성

일부 응용 프로그램은 하나 이상의 속성을 행 집합의 열로 승격함으로써 XML 데이터에서 행 집합을 생성해야 합니다. 예를 들어, 응용 프로그램은 책의 저자를 쿼리하고 성 및 이름에 대해 두 개의 열을 포함하는 테이블로 결과를 표시할 수 있습니다. 이러한 행 집합 생성은 서로 다른 성능 특성을 지닌 서버 또는 클라이언트에서 모두 수행될 수 있습니다.

• XML 데이터 형식의 nodes() 및 value() 메서드의 결합
• OpenXML
• CLR(공용 언어 런타임)에서 테이블 반환 함수 스트리밍