SQL Server 2005 개발자 가이드[9/13]

XQuery 지원기능

XQuery는 XML에 대한 쿼리 언어입니다. XML 데이터를 조회하기 위한 XQuery 표현식 기반 xml 데이터형의 메서드를 살펴보고 XQuery 구문에 대해서 살펴봅니다. XQuery 구문에는 XPath 2.0 표현식이 포함되어 있으며, XQuery를 사용하여 XML 데이터원본에 복잡한 쿼리를 실행할 수 있습니다. SQL Server 에 제공하는 xml 데이터형에서는 XQuery 표현식을 사용하여 xml 데이터를 조회하고 변경하기 위해서 사용할 수 있는 메서드를 제공합니다. SQL Server 2005의 XQuery 지원기능은 W3C XQuery 1.0 언어 스펙을 기초로 합니다. ( http://www.w3.org/XML/Query 참조)

XQuery 구문

XQuery는 크게 두가지 부분으로 구성됩니다. 네임스페이스를 선언하고, 스키마를 추가하기 위한 머리글 부분(생략가능)과 xml 데이터를 조회하기 위해서 사용하는 실제 XQuery 표현식이 포함된 본문 부분으로 나눌 수 있습니다. XQuery 표현식은 조회하고자 하는 XML 노드에 대한 간략한 경로 정보일 수도 있고, XML 결과를 생성하기 위한 복잡한 표현식일 수도 있습니다. XQuery 경로는 XPath 언어를 기반으로 하며, XML 문서상에서 조회하고자 하는 노드의 위치를 나타냅니다. 경로는 절대경로(루트 엘리먼트로부터 XML 트리의 특정 노드의 위치를 표현)로 지정될 수도 있고, 상대경로(이미 알려진 노드로부터 조회하고자 하는 노드의 위치를 상대적으로 표현)로 지정될 수도 있습니다. 다음의 표는 간단한 XQuery 경로 예제를 나타냅니다. 예제 경로 설명 /InvoiceList/Invoice <InvoiceList> 루트 엘리먼트에 포함되어 있는 모든 <Invoice> 엘리먼트 (/InvoiceList/Invoice) [2] <InvoiceList> 루트 엘리먼트에 포함되어 있는 두번째 <Invoice> 엘리먼트 (InvoiceList/Invoice/@InvoiceNo) [1] <InvoiceList> 루트 엘리먼트에 포함되어 있는 첫번째 <Invoice> 엘리먼트의 InvoiceNo 속성 (InvoiceList/Invoice/Customer/text( ))[1] <InvoiceList> 루트 엘리먼트에 포함되어 있는 <Invoice> 엘리먼트의 하위 첫번째 <Customer> 엘리먼트의 텍스트 /InvoiceList/Invoice[@InvoiceNo=1000 <InvoiceList> 루트 엘리먼트에 포함되어 있는 모든 <Invoice> 엘리먼트 중에서 InvoiceNo 속성이 1000인 <Invoice> 엘리먼트

FLOWR 문장

XQuery 언어 스펙에 포함되어 있는 for, let, order by, where, return 문장을 통상적으로 FLOWR “( flower”라고 읽음) 라고 부릅니다. SQL Server 2005에서는 for, where, return 문장을 지원하며, 각각의 사용용도는 다음과 같습니다. 문장 설명 For XML 문서의 동일 수준의 노드를 반복하며 처리하기 위해서 사용합니다. Where 노드를 반복처리할 때 필터링 조건을 지정하기 위해서 사용합니다. XQuery 언어에는 Where 문장과 함께 사용할 수 있는 count( )와 같은 함수가 포함되어 있습니다. Return 반복구조내에서 반환할 XML을 지정하기 위해서 사용합니다. 다음의 예제는 각 <Invoice> 엘리먼트별로 <Items> 자식 엘리먼트에 포함된 하나 이상의 <Item> 엘리먼트의 목록을 반환합니다. for $i in /InvoiceList/Invoice where count($i/Items/Item) > 1 return $i

XQuery 표현식 사용예제

XQuery 표현식 중 가장 일반적으로 사용되는 유형을 예제와 함께 소개합니다. 원본XML <InvoiceList> <Invoice InvoiceNo=”1000”> <Customer>Kim Abercrombie</Customer> <Items> <Item Product=”1”Price=”1.99”Quantity=”2”/> <Item Product=”3”Price=”2.49”Quantity=”1”/> </Items> </Invoice> <Invoice InvoiceNo=”1001”> <Customer>Sean Chai</Customer> <Items> <Item Product=”1”Price=”1.99”Quantity=”2”/> </Items> </Invoice> </InvoiceList> ■ 단순한 XQuery 표현식 사용 구문 : /InvoiceList/Invoice 결과값 <Invoice InvoiceNo=”1000”> <Customer>Kim Abercrombie</Customer> <Items> <Item Product=”1”Price=”1.99”Quantity=”2”/> <Item Product=”3”Price=”2.49”Quantity=”1”/> </Items> </Invoice> <Invoice InvoiceNo=”1001”> <Customer>Sean Chai</Customer> <Items> <Item Product=”1”Price=”1.99”Quantity=”2”/> </Items> </Invoice> ■ XQuery 조건 사용 구문 /InvoiceList/Invoice[@InvoiceNo=1000] 결과값 <Invoice InvoiceNo=”1000”> <Customer>Kim Abercrombie</Customer> <Items> <Item Product=”1”Price=”1.99”Quantity=”2”/> <Item Product=”3”Price=”2.49”Quantity=”1”/> </Items> </Invoice> ■ For 문장과 Return 문장 사용 구문 for $i in /InvoiceList/Invoice/Items/ Item[../../@InvoiceNo=1000] return $i 결과값 <Item Product=”1”Price=”1.99”Quantity=”2”/> <Item Product=”3”Price=”2.49”Quantity=”1”/> ■ For 문장과 Return 문장을 사용하여 XML 생성 구문 <OrderedItems> { for $i in /InvoiceList/Invoice/Items/Item return $i } </OrderedItems> 결과값 <OrderedItems> <Item Product=”1”Price=”1.99”Quantity=”2”/> <Item Product=”3”Price=”2.49”Quantity=”1”/> <Item Product=”1”Price=”1.99”Quantity=”2”/> </OrderedItems> ■ For 문장과 Return 문장을 사용하여 속성과 값을 반환 구문 <OrderedItems> { for $i in /InvoiceList/Invoice/Items/Item return <Product> {$i/@Quantity} {string($i/@Product)} </Product> } </OrderedItems> 결과값 <OrderedItems> <Product Quantity=”2”>1</Product> <Product Quantity=”1”>3</Product> <Product Quantity=”2”>1</Product> </OrderedItems> ■ For, Where, Return 문장 사용 구문 <MultiItemInvoices> { for $i in /InvoiceList/Invoice where count($i/Items/Item) > 1 return $i } </MultiItemInvoices> 결과값 <MultiItemInvoices> <Invoice InvoiceNo=”1000”> <Customer>Kim Abercrombie</Customer> <Items> <Item Product=”1”Price=”1.99”Quantity=”2”/> <Item Product=”3”Price=”2.49”Quantity=”1”/> </Items> </Invoice> </MultiItemInvoices> ■ 머리글에 네임스페이스 지정 구문 declare namespace awi = “http://schemas.adventure-works.com/Invoices”; /awi:InvoiceList/awi:Invoice[@InvoiceNo=1000] 결과값 <awi:Invoice xmlns:awi=”http://schemas.adventure-works.com/Invoices” InvoiceNo=”1000”> <awi:Customer>Kim Abercrombie</awi:Customer> <awi:Items> <awi:Item Product=”1”Price=”1.99”Quantity=”2”/> <awi:Item Product=”3”Price=”2.49”Quantity=”1”/> </awi:Items> </awi:Invoice> ■ 기본 네임스페이스 사용 구문 declare default namespace = “http://schemas.adventure-works.com/Invoices”; /InvoiceList/Invoice[@InvoiceNo=1000] 결과값 <Invoice xmlns=”http://schemas.adventure-works.com/Invoices” InvoiceNo=”1000”> <Customer>Kim Abercrombie</Customer> <Items> <Item Product=”1”Price=”1.99”Quantity=”2”/> <Item Product=”3”Price=”2.49”Quantity=”1”/> </Items> </Invoice>

XML 데이터형에서 제공하는 메서드를 사용하여 쿼리실행

SQL Server 2005 XML 데이터형은 XML 데이터를 쿼리하고 수정하기 위해서 사용할 수 있는 네 가지 메서드를 제공합니다. 각 메서드는 대부분의 개발자에게 익숙한 구문인 데이터형.메서드_명칭으로 호출할 수 있습니다. 각 메서드의 기능에 대해서 이해하면, XML 데이터를 데이터베이스에서 처리하는 어플리케이션을 개발하는데 도움이 될 것입니다. ■ Query 메서드 Query 메서드는 XML 데이터형에 저장된 데이터에서 XML을 추출하기 위해서 사용합니다. Query 메서드의 매개변수로 전달되는 XQuery 표현식에 지정된 결과값이 조회됩니다. SELECT xmlCol.query‘( declare default namespace = “http://schemas.adventure-works.com/InvoiceList”; <InvoiceNumbers> { for $i in /InvoiceList/Invoice return <InvoiceNo> {number($i/@InvoiceNo)} </InvoiceNo> } </InvoiceNumbers>’ ■ Value 메서드 Value 메서드는 XML 문서로부터 단일 값을 반환하기 위해서 사용합니다. value 메서드를 사용하기 위해서는 XQuery 표현식을 XML 데이터에 포함된 단일 노드를 식별할 수 있는 형태로 지정해야 하며, 반환되는 값이 T-SQL 데이터형이 되도록 지정해야 합니다. SELECT xmlCol.value‘( declare default namespace = “http://schemas.adventure-works.com/InvoiceList”; /InvoiceList/Invoice/@InvoiceNo)[1]’,‘ int’ ■ Exist 메서드 Exist 메서드는 XML 문서에 지정된 노드가 존재하는지 여부를 판단하기 위해서 사용합니다. Exist 메서드가 1을 반환하면, 지정된 노드가 XML 문서내에서 하나 또는 그 이상 존재한다는 것을 의미하며, 0을 반환하면, 지정된 노드가 존재하지 않는다는 것을 의미합니다. SELECT xmlCol.exist‘( declare default namespace = “http://schemas.adventure-works.com/InvoiceList”; /InvoiceList/Invoice[@InvoiceNo=1000]’ ■ 관계형 테이블의 컬럼과 변수의 바인딩 SQL Server 2005에서는 XQuery 언어를 사용하여 xml 데이터형 컬럼을 조회하기 위한 메서드가 포함된 SELECT 문장에서, 관계형 데이터 컬럼을 참조할 수 있도록 지원합니다. XML 데이터형 컬럼으로부터 XML 데이터를 조회하기 위해, XML 데이터형 메서드가 포함된 SELECT 문장에서, sql:column 함수를 사용하여, XML 데이터안에 비-xml 데이터 컬럼값을 포함시킬 수 있습니다. 또한, sql:variable 확장을 사용하여, 저장 프로시저내에서 변수를 참조할 수 있습니다. XML 데이터내부에 비-xml 컬럼 값을 포함시키기 위해, sql:column 함수를 사용하는 예제는 다음과 같습니다. SELECT StoreName, Invoices.query‘( declare default namespace= “http://schemas.adventure-works.com/Invoices”; <Invoices> <Store>{sql:column“( StoreName”)}</Store> { for $i in /InvoiceList/Invoice return $i } </Invoices>’) InvoicesWithStoreName FROM Stores

Modify 메서드를 사용하여 XML 데이터 변경

XML 데이터형 컬럼에 저장된 XML 데이터를 변경하기 위해서 Modify 메서드를 사용합니다. Modify 메서드는 XQuery 언어 스펙에 대해 insert, replace, delete 확장기능을 지원합니다. 세 가지 확장기능은 XML DML로서 참조할 수 있습니다.    ㆍ INSERT 문장을 사용하여 XML 데이터에 노드를 추가할 수 있습니다.    ㆍ REPLACE 문장을 사용하여 XML 데이터를 변경할 수 있습니다.    ㆍ DELETE 문장을 사용하여 XML 데이터에서 특정 노드를 삭제할 수 있습니다. ■ INSERT 문장 Modify 메서드와 함께 INSERT 문장을 사용하여, XML 데이터형 컬럼이나 변수에 저장된 XML 데이터에 노드를 추가할 수 있습니다. INSERT 문장에 대한 구문은 다음과 같습니다. insert Expression1 ( {as first | as last} into | after | before Expression2 ) INSERT 키워드에 지정할 수 있는 매개변수는 다음과 같습니다. 매개변수 설명 Expression1 추가될 노드를 지정하기 위한 표현식으로, XML 문자열 형식으로 지정해야 합니다. (예를 들어,<Item Product=”5”Quantity=”1”/>) 또한, 텍스트 노드에 추가할 엘리먼트 표현식을 지정할 수도 있습니다. (예를 들어, element SalesPerson {“ Bill”}) 마지막으로, 속성에 추가할 속성 표현식을 지정할 수 있습니다. (예를 들어, attribute discount {“ 1.50”}) as first 계층구조의 첫번째 노드에 새로운 XML을 추가하기 위해서 사용합니다. as last 계층구조의 마지막 노드에 새로운 XML을 추가하기 위해서 사용합니다. Into Expression2 위치에 Expression1을 추가하기 위해서 사용합니다. After Expression2 뒤에 Expression1을 추가하기 위해서 사용합니다. Before Expression2 앞에 Expression1을 추가하기 위해서 사용합니다. Expression2 XML 문서에 포함된 기존 노드를 지정하기 위한 XQuery 표현식. 다음 예제는 Modify 메서드와 함께 INSERT Xquery 문장을 사용하는 방법을 나타냅니다. SET @xmlDoc.modify ‘( declare default namespace = “ http://schemas.adventureworks. com/InvoiceList”; insert element salesperson “{ Bill”} as first into (/InvoiceList/Invoice)[1]’ ■ replace 문장 XML 데이터를 변경하기 위해서 modify 메서드와 함께 REPLACE 문장을 사용합니다. REPLACE 문장에 대한 구문은 다음과 같습니다. replace value of Expression1 with Expression2 REPLACE 문장에서 사용할 수 있는 매개변수는 다음과 같습니다. 매개변수 설명 Expression1 값을 변경할 노드를 지정하기 위한 XQuery 표현식 Expression2 대체할 노드에 새로 지정할 값 다음의 예제는 Modify 메서드와 함께 REPLACE 문장을 사용하는 방법을 나타냅니다. SET xmlCol.modify ‘( declare default namespace =”http://schemas.adventure-works.com/InvoiceList”; replace value of (/InvoiceList/Invoice/SalesPerson/text( ))[1] with“ Ted”’) ■ DELETE 문장 XML 데이터에서 지정된 노드를 삭제하기 위해서 Modify메서드와 함께 DELETE 문장을 사용합니다. DELETE 문장에 대한 구문은 다음과 같습니다. delete Expression Expression 매개변수는 삭제할 노드를 지정하기 위한 XQuery 표현식입니다. 다음 예제는 modify 메서드와 함께 DELETE 문장을 사용하는 방법을 나타냅니다. SET xmlCol.modify ‘( declare default namespace =”http://schemas.adventure-works.com/InvoiceList”; delete (/InvoiceList/Invoice/SalesPerson)[1]’)

Nodes 메서드를 사용하여 XML 데이터 부분추출

Xml 데이터형에서는 nodes 메서드를 통해, XML 데이터를 관계형 테이블 형식으로 생성할 수 있는 기능을 제공합니다. Nodes 메서드는 XQuery 표현식으로 지정된 각 노드를 행집합 형식으로 반환합니다. nodes 메서드의 구문은 다음과 같습니다. xmlvalue.nodes (XQuery) [AS] Table(Column) Nodes 메서드에서 사용할 수 있는 매개변수는 다음과 같습니다. 매개변수 설명 Xmlvalue XML 데이터형 변수 또는 컬럼 XQuery 반환하고자 하는 노드를 지정하기 위한 XQuery 표현식 Table(Column) 결과값으로 반환할 테이블명과 컬럼명. 결과값 테이블은 순차적으로 수행되는 쿼리에서 데이터를 추출하기 위한 원본으로 사용될 수 있습니다. Xml 데이터형 변수나 컬럼에서 nodes 메서드를 사용하여 관계형 테이블 형식의 데이터를 조회할 수 있습니다.    ㆍ XML 데이터형 변수에서 관계형 테이블 형식의 데이터를 추출하기 위해서, nodes 메서드에서        반환하는 결과행집합에 대해서, query, value, exist와 같은 메서드를 사용합니다.    ㆍ Xml 데이터형 컬럼에서 관계형 테이블 형식의 데이터를 반환하기 위해서, nodes 메서드와 함께        APPLY 연산자를 사용합니다. ■ XML 변수에 대해 nodes 메서드 사용법 Xml 데이터형 변수에서 관계형 테이블 형식의 데이터를 추출하기 위해서, nodes 메서드에서 반환하는 행집합에 query, value, exist 메서드를 사용할 수 있습니다. 다음 예제는 xml 변수에서 관계형 테이블 형식의 주문 데이터를 추출하는 방법을 나타냅니다. DECLARE @xmlOrder xml SET @xmlOrder =‘ <?xml version=”1.0”?> <Order OrderID=”1000”OrderDate=”2005-06-04”> <LineItem ProductID=”1”Price=”2.99”Quantity=”3”/> <LineItem ProductID=”2”Price=”3.99”Quantity=”1”/> </Order>’ SELECT nCol.value‘( @ProductID’,‘ integer’) ProductID, nCol.value‘( @Quantity’,‘ integer’) Quantity FROM @xmlOrder.nodes‘( /Order/LineItem’) AS nTable(nCol) 위의 코드를 실행하면 다음과 같은 결과가 반환됩니다. ProductID Quantity 1 3 2 1 ■ XML 컬럼에 대해 nodes 메서드 사용하는 방법 Xml 컬럼에서 관계형 테이블 형식의 데이터를 반환하게 하기 위해서, nodes 메서드와 함께 APPLY 연산자를 사용합니다. 다음 예제는 nodes 메서드를 사용하여 XML 컬럼으로부터 주문 데이터를 추출하는 방법을 나타냅니다. SELECT nCol.value‘( ../@OrderID[1]’,‘ int’) OrderID, nCol.value‘( ../@OrderDate[1]’,‘ datetime’) OrderDate, nCol.value‘( @ProductID[1]’,‘ int’) ProductID, nCol.value‘( @Price[1]’,‘ money’) Price, nCol.value‘( @Quantity[1]’,‘ int’) Quantity FROM Orders_X CROSS APPLY OrderDoc.nodes‘( /Order/LineItem’) AS nTable(nCol) 위의 코드는 다음과 같은 결과값을 반환합니다. OrderID OrderDate ProductID Price Quantity 1000 2005-06-04 00:00:00.000 1 2.99 1 1000 2005-06-04 00:00:00.000 2 3.99 2 1000 2005-06-04 00:00:00.000 2 3.99 1 1002 2005-06-04 00:00:00.000 1 3.99 1

[출처] DBGuide.net