XML 데이터 조작 FOR XML 절을 이용하여 조회 결과를 XML 문서로 얻는 방법을 이해한다. OPENXML을 이용하여 XML 문서를 조작하여 행 집합을 얻는 방법을 이해한다. 다른 데이터 형식처럼 고유한 XML 데이터 형식을 사용하는 방법을 이해한다. 스키마 컬렉션, 문서와 콘텐츠, Xquery와 XML 데이터 형식 메서드를 이해한다.

XML 데이터 조작 FOR XML 절 OPENXML XML 데이터 형식

1. FOR XML 절 개요 XML은 그 자체로 인간의 지식을 표현하는 간편하고도 강력한 수단이 될 뿐만 아니라, 서로 다른 플랫폼 사이에서 정보를 교환 해주는 용도로도 점점 더 많이 사용되고 있다. 따라서 XML 데이터를 저장하고 조작할 수 있는 기능도 DBMS의 필수 조건이 되어가고 있다.

1. FOR XML 절 개념 SELECT 문에 FOR XML 절을 포함하여 표준적인 행 집합 대신 FOR XML 절은 XML 기능 확장을 위해 이미 오래 전에 SQL Server에 추가되었다. 테이블에 대해 FOR XML 절을 포함한 SELECT 문을 실행하면 [그림 11-1]의 하단과 같은 XML 문서를 반환하는데, 이를 웹 응용 프로그램이나 기타 전자 상거래용으로 사용할 수 있다.

1. FOR XML 절 개념

1. FOR XML 절 작성 정식 XML 문서로 변환 실제로 [그림 11-1]에서 생성한 것은 정확한 XML 문서가 아니며, 첫째 행은 XML 프롤로그(prolog)라고 하는데, XML 문서임을 나타 내면서 XML의 버전과 인코딩하는 언어(한국어)를 지정한다. 여기서 <document>는 최상위 수준에 유일하게 존재해야 하는 루트 요소 (root element)를 나타낸다.

1. FOR XML 절 작성 정식 XML 문서로 변환 <?xml version="1.0"encoding="euc-kr"?> <document> <jobs job_id="1"min_lvl="10"max_lvl="10"/> <jobs job_id="2"min_lvl="200"max_lvl="250"/> ... <jobs job_id="14"min_lvl="25"max_lvl="100"/> </document>

1. FOR XML 절 작성 FOR XML 절의 구문

1. FOR XML 절 작성 <CommonDirectives>의 구문 RAW, AUTO, EXPLICIT 및 PATH는 XML 모드(mode)에 해당하는 것으로 XML 문서의 형식을 결정한다(이는 뒤에서 자세히 설명할 것임). XMLDATA 옵션은 인라인 XDR(XML-Data Reduced) 스키마를 반환해야 함을 나타낸다. 인라인 스키마는 문서 앞에 놓인다.

1. FOR XML 절 작성 ELEMENTS 옵션을 사용하면 열이 하위 요소(element)에 매핑된다. 이 옵션이 지정되지 않으면 열이 속성(attribute)에 매핑된다. 이 옵션 은 RAW, AUTO 및 PATH 모드에서만 사용할 수 있다. BINARY BASE64 옵션이 지정되면 반환되는 이진 데이터가 모두 base64 인코딩 형식으로 표시된다. RAW와 EXPLICIT 모드를 사용 하여 이진 데이터를 검색하려면 이 옵션을 지정해야 한다. AUTO 모드 에서는 기본적으로 이진 데이터는 참조로 반환된다.

1. FOR XML 절 작성 FOR XML 절이 사용될 수 있는 곳과 제약 사항은 다음과 같다. FOR XML 절은 최상위 쿼리의 SELECT 문 및 하위 쿼리(subquery) 의 INSERT, UPDATE 및 DELETE 문에서 사용할 수 있다. COMPUTE BY 절과 함께 사용할 수 없다.

1. FOR XML 절 조작 RAW 모드 RAW 모드는 결과 집합의 각 행을 row라는 이름의 XML 요소로 변환한다. NULL이 아닌 각 열 값은 열 이름과 같은 XML 요소의 속성에 매핑된다. 이진 데이터를 base64 인코딩 형식으로 반환하려면 BINARY BASE64 옵션을 지정한다.

1. FOR XML 절 조작 RAW 모드에서 BINARY BASE64 옵션을 지정하지 않고 이진 데이터를 검색하면 오류가 발생한다. XMLDATA 옵션으로 XML 데이터 스키마를 요청하면 이름 공간으로 선언된 스키마가 데이터 시작 부분에 나타나고, 모든 최상위 요소에 대해 스키마 이름 공간에 대한 참조가 반복된다.

1. FOR XML 절 조작 RAW 모드를 사용해보자. 예제 1 1 USE Test1DB; 2 SELECT o.orders_id, b.buyer_name, p.product_name, o.qty 3 FROM orders AS o 4 INNER JOIN buyer AS b 5 ON o.buyer_id = b.buyer_id 6 INNER JOIN product AS p 7 ON o.product_id = p.product_id 8 FOR XML RAW, XMLDATA;

1. FOR XML 절 조작

1. FOR XML 절 조작

1. FOR XML 절 조작 이 절의 모든 실행 결과는 별도의 출력 결과 파일로 저장했다. 예를 들어, [예제 11]의 출력 결과 파일은‘11-01_Raw1_out.txt’ 다‘( 11-01_Raw1.sql’은 행 번호가 없는 스크립트고, ‘ 11-01_Raw1.txt’는 행 번호가 붙은 스크립트다).

1. FOR XML 절 조작 varbinary(MAX) 형식의 열 값을 base64 인코딩 형식으로 반환해보자. 예제 2 1 USE AdventureWorksLT; 2 SELECT TOP 2 ProductID, ThumbNailPhoto 3 FROM SalesLT.Product 4 FOR XML RAW, BINARY BASE64;

1. FOR XML 절 조작

1. FOR XML 절 조작 XML 데이터의 출력 및 포맷 행으로 매우 길게 나타난다. 따라서 결과가 잘리지 않도록 하려면 SSMSE의 [도구 | 옵션]을 선택한 후, [쿼리 결과 | SQL Server | 텍스트로 결과 표시]를 선택하고‘각 열에 표시할 최대 문자 수’를 최댓값인 8192로 수정한 후, [확인]을 클릭한다. 만약 적용되지 않는다면 문서 창을 닫고 새로 열어보면 확인할 수 있다.

1. FOR XML 절 조작 쿼리 편집기의 결과 창이 표 모드일 경우, XML 데이터는 [그림 11-2] 데이터 문서가 열린다.

1. FOR XML 절 조작

1. FOR XML 절 조작 그런데 XML 데이터가 복잡해지면 [그림 11-3]처럼 완벽하게 포맷되지 않는 경우가 많은데, 이 경우 XML Spy(http://www.altova.com/ download/xmlspy/xml_editor_enterprise.html)와 같은 도구를 이용하면 완벽하게 포맷할 수 있다. XML Spy에 XML 데이터를 복사하고, 여기에 XML 프롤로그와 루트 요소를 추가한 후, [Edit | Pretty-Print XML Text] 메뉴 항목을 선택하면 전체가 한 번에 포맷이 된다.

1. FOR XML 절 조작 AUTO 모드 AUTO 모드는 쿼리 결과를 중첩된 XML 요소로 반환한다. 최소한 한 개의 열이 SELECT 절에 포함되는 FROM 절의 각 테이블은 XML 요소로 표시된다. 기본적으로 SELECT 절의 열은 해당 요소의 속성에 매핑된다. 그러나 ELEMENTS 옵션을 지정하면 열이 요소의 속성 대신 하위 요소로 매핑된다.

1. FOR XML 절 조작 XML 문서의 계층 구조(중첩된 요소)는 SELECT 절의 열이 소속된 테이블의 순서를 바탕으로 하기 때문에 열의 순서가 중요하다. 테이블이 매핑된 요소는 열 이름이 나열되는 순서대로 식별되고 중첩된다. 첫 번째로 식별되는 테이블은 XML 문서의 최상위 요소를 만들고, 두 번째 로 식별되는 테이블은 최상위 요소의 하위 요소를 만든다. 그러나 SELECT 절의 열 이름이 소속된 테이블이 이미 식별되어 요소로 매핑되었을 경우에는, 새로운 요소를 만드는 대신에 이미 만들어진 요소의 속성으로 추가되거나 ELEMENTS 옵션을 지정한 경우 하위 요소로 추가된다.

1. FOR XML 절 조작 AUTO 모드로 세 개의 테이블을 조인한 결과 집합을 받아보자. 예제 3 1 USE Test1DB; 2 SELECT o.orders_id, b.buyer_name, p.product_name, o.qty 3 FROM orders AS o 4 INNER JOIN buyer AS b 5 ON o.buyer_id = b.buyer_id 6 INNER JOIN product AS p 7 ON o.product_id = p.product_id 8 FOR XML AUTO;

1. FOR XML 절 조작

1. FOR XML 절 조작

1. FOR XML 절 조작 [예제 3]에 ELEMENTS 옵션을 추가해보자. 예제 4 1 USE Test1DB; - 열이 요소의 속성 대신 하위 요소로 매핑된 것을 알 수 있음 예제 4 1 USE Test1DB; 2 SELECT o.orders_id, b.buyer_name, p.product_name, o.qty 3 FROM orders AS o 4 INNER JOIN buyer AS b 5 ON o.buyer_id = b.buyer_id 6 INNER JOIN product AS p 7 ON o.product_id = p.product_id 8 FOR XML AUTO, ELEMENTS;

1. FOR XML 절 조작

1. FOR XML 절 조작

1. FOR XML 절 조작

1. FOR XML 절 조작 PATH 모드 PATH 모드는 요소와 특성을 혼합하고 추가 중첩을 사용하여 복잡한 속성을 간단히 표현할 수 있다. EXPLICIT 모드보다 훨씬 간단한 표현 방법을 제공한다. XML 유형 인스턴스를 반환하는 중첩 FOR XML 쿼리 및 TYPE 지시어 작성 기능과 함께 PATH 모드를 사용하면 좀 더 간편하게 쿼리를 작성 할 수 있다.

1. FOR XML 절 조작 기본적으로 PATH 모드는 결과 집합의 각 행에 대한 <row> 요소 래퍼를 생성하며, 필요에 따라 요소 이름을 지정할 수 있다. 요소 이름을 지정하면 지정된 이름이 래퍼 요소 이름으로 쓰이며, 빈 문자열 (FOR XML PATH‘( ’))을 지정하면 래퍼 요소가 생성되지 않는다.

1. FOR XML 절 조작 PATH 모드로 세 개의 테이블을 조인한 결과 집합을 받아보자. 예제 5 각 행에 대한 <row> 요소 래퍼가 생성되며, 모든 열이 대등한 하위 요소들로 매핑된 것을 알 수 있음 예제 5 1 USE Test1DB; 2 SELECT o.orders_id, b.buyer_name, p.product_name, o.qty 3 FROM orders AS o 4 INNER JOIN buyer AS b 5 ON o.buyer_id = b.buyer_id 6 INNER JOIN product AS p 7 ON o.product_id = p.product_id 8 FOR XML PATH;

1. FOR XML 절 조작

1. FOR XML 절 조작

1. FOR XML 절 조작 ‘Order’로 행 요소 이름을 지정해보자. 예제 6 1 USE Test1DB; 2 SELECT o.orders_id, b.buyer_name, p.product_name, o.qty 3 FROM orders AS o 4 INNER JOIN buyer AS b 5 ON o.buyer_id = b.buyer_id 6 INNER JOIN product AS p 7 ON o.product_id = p.product_id 8 FOR XML PATH ('Order');

1. FOR XML 절 조작

1. FOR XML 절 조작 EXPLICIT 모드 EXPLICIT 모드는 반환되는 XML 문서의 모양을 개인화(customize) 하는 모드로, 원하는 XML 문서 형태를 얻기 위해 상당히 복잡한 쿼리를 작성해야 한다.

2. OPENXML 개념 OPENXML은 메모리 내의 XML 문서에 대한 행 집합을 제공하는 T-SQL 키워드다. OPENXML은 행 집합 반환 함수(예를 들어, OPENROWSET이나 테이블 반환 사용자 정의 함수들)와 흡사하게 작용한다. OPENXML이 돌려주는 행 집합은 SELECT INTO 문 등을 사용하여 데이터베이스 테이블에 저장할 수도 있다.

2. OPENXML 개념 OPENXML의 개념도는 [그림 11-4]와 같다.

2. OPENXML 개념 OPENXML을 사용하여 XML 문서에 대한 쿼리를 작성하려면 먼저 XML 문서를 구문 분석하여 메모리상에 XML DOM(Document Object Model)을 만들고, 이에 대한 핸들(handle)을 반환하는 저장 프로시저 p_xml_preparedocument를 호출해야 한다. XML DOM은 XML 문서의 여러 노드(요소, 속성, 텍스트, 주석 등)를 트리로 표시한다. XML DOM에 대한 핸들이 OPENXML로 전달되면 이를 기반으로 XML 문서를 조각내어 행 집합을 만들어 반환한다.

2. OPENXML 개념 메모리 낭비를 막기 위해 조작이 끝난 XML DOM은 저장 프로시저 sp_xml_removedocument를 호출하여 메모리에서 제거해야 한다. OPENXML과 관련된 구문 @idoc : MSXML 파서가 XML 문서 xmltext를 구문 분석한 후 반환 하는 XML DOM에 대한 핸들이다. @idoc은 int 형의 변수로 미리 선언되어야 한다.

2. OPENXML 개념 xmltext : 원본 XML 텍스트 문서다. xmltext의 데이터 형은 char, nchar, varchar, nvarchar, text, ntext 또는 xml이 가능하다. xpath_namespaces : OPENXML의 행과 열 XPath 식에서 사용되는 이름 공간 선언을 지정하는 것으로 생략할 수 있다. rowpattern : 행으로 처리될 노드를 식별하기 위해 사용되는 XPath 패턴이다. flag : XML 데이터와 관계형 행 집합 사이에 사용해야 하는 매핑과 남는 열을 채우는 방법을 나타내는 플래그로([표 11-1] 참고), tinyint 형이며 생략 가능하다.

2. OPENXML 개념

2. OPENXML 개념 SchemaDeclaration은 다음과 같은 형식의 스키마 정의다. ColName : 행 집합의 열 이름이다. ColType : 열의 SQL 데이터 형이다. 이것이 속성의 XML 데이터 형과 다른 경우에는 형 강제 변환이 발생한다. 열의 형이 timestamp인 경우 에는XML 문서의 현재 값이 무시되고 자동 채우기 값이 반환된다.

2. OPENXML 개념 ColPattern : 선택 사항이며, XML 노드를 열에 어떤 방법으로 매핑해야 하는지를 설명하는 일반적인 XPath 패턴이다. ColPattern이 지정되지 않은 경우에는 기본 매핑(flags에 의해 지정된 속성 중심 또는 요소 중심의 매핑)을 사용한다. ColPattern으로 지정된 XPath 패턴은 flags로 표시된 기본 매핑을 개선하거나 덮어쓰는 특별한 매핑을 지정하는 데 쓰인다. ColPattern으로 지정된 일반 XPath 패턴은 메타 속성도 지원한다.

2. OPENXML 개념 TableName : 원하는 스키마가 있는 테이블이 이미 존재하고 열 패턴이 MetaProperty : OPENXML이 제공하는 메타 속성 중 하나다. 메타 속성이 지정된 경우, 열에는 메타 속성이 제공하는 정보가 포함된다. 이러한 메타 속성을 사용하면 텍스트로 표시되는 것보다 더 상세한 XML 노드 정보(상대 위치, 이름 공간 정보)를 추출할 수 있다. TableName : 원하는 스키마가 있는 테이블이 이미 존재하고 열 패턴이 필요하지 않을 때 SchemaDeclaration 대신 지정할 수 있는 테이블 이름이다.

2. OPENXML 조작 OPENXML로 XML 문서를 조작하는 방법을 익혀보자. 예제 7 1 DECLARE @hdoc int; 2 DECLARE @xmltext varchar(2000); 3 SET @xmltext = 4 '<ROOT> 5 <Customer CustomerID="VINET" 6 ContactName="Paul Henriot"> 7 <Order CustomerID="VINET" EmployeeID="5" 8 OrderDate="1996-07-04T00:00:00"> 9 <OrderDetail OrderID="10248“ 10 ProductID="11" Quantity="12"/> 11 <OrderDetail OrderID="10248“ 12 ProductID="42" Quantity="10"/>

2. OPENXML 조작 13 </Order> 14 </Customer> 15 <Customer CustomerID="LILAS" 16 ContactName="Carlos Gonzlez"> 17 <Order CustomerID="LILAS" EmployeeID="3" 18 OrderDate="1996-08-16T00:00:00"> 19 <OrderDetail OrderID="10283" 20 ProductID="72" Quantity="3"/> 21 </Order> 22 </Customer> 23 </ROOT>'; 24 EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmltext; 25 SELECT * 26 FROM OPENXML(@hdoc, '/ROOT/Customer', 1) 27 WITH ( 28 CustomerID varchar(10) 29 , ContactName varchar(20) 30 ); 31 EXEC sp_xml_removedocument @hdoc;

2. OPENXML 조작

2. OPENXML 조작 OPENXML로 XML 문서를 처리한 후, 행 집합을 테이블에 예제 8 저장해보자. 1 USE Test1DB; 2 DECLARE @hdoc int, @xmltext varchar(2000); 3 SET @xmltext = 4 '<?xml version="1.0" encoding="euc-kr"?> 5 <members> 6 <member num="A01" alias= "번개"> 7 <name>홍길동</name> 8 <phone>02-111-2323</phone> 9 </member> 10 <member num="A02" alias= "바람"> 11 <name>을지매</name> 12 <phone>02-333-2323</phone> 13 </member>

2. OPENXML 조작 14 <member num="A03" alias= "구름"> 15 <name>김삿갓</name> 16 <phone>02-123-3456</phone> 17 </member> 18 </members>'; 19 EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmltext; 20 SELECT * 21 INTO OpenXmlTable 22 FROM OPENXML(@hdoc, '/members/member', 1) 23 WITH ( 24 ID char(3) '@num' 25 , who varchar(10) 'name' 26 , tel varchar(13) 'phone' 27 ); 28 EXEC sp_xml_removedocument @hdoc; 29 GO 30 SELECT * FROM OpenXmlTable;

2. OPENXML 조작

3. XML 데이터 형식 개요 SQL Server 2000까지만 하더라도 XML 데이터는 하나의 문자열 형식의 열로 저장하거나 OPENXML을 이용하여 여러 개의 데이터로 분할한 후 문자열 형식의 열들로 저장할 수밖에 없었다. SQL Server 2005부터 도입된 고유한 XML 데이터 형식을 이용하면 문자열 형식에 비해 저장 공간을 절약할 뿐만 아니라, XML 열에 대한 인덱싱 및 전체 텍스트 검색(full-text search)을 통한 성능 향상, XML 스키마 컬렉션과 XML 제약조건을 통한 강력한 형식 검증, XQuery를 통한 조작성 개선 등의 많은 이점이 있다.

3. XML 데이터 형식 개요 XML 형식의 데이터는 내부적으로 요소(element) 및 속성 (attribute) 마크업이 제거된 이진 형식으로 저장된다. 예) 아래 XML 데이터 중 <bookstore>, </bookstore>, <book>, </book> 등이 요소 마크업이고, category=“ ”가 속성 마크업이다. 이 마크업 자체는 계속 반복되는 특징이 있으므로 이를 제거하면 많은 공간이 절약된다.

3. XML 데이터 형식 개요 <bookstore> <book category="CHILDREN“> <title>Harry Potter</title> <author>J K. Rowling</author> <year>2005</year> <price>29.99</price> </book> <book category="WEB"> <title>Learning XML</title> <author>Erik T. Ray</author> <year>2003</year> <price>39.95</price> </bookstore>

3. XML 데이터 형식 개요 XML 형식의 데이터가 이진 형식으로 저장되는 이유는 압축을 쉽게 문자열 형식의 데이터보다 더 작은 저장 공간을 차지하는 것을 확인 해보자.

3. XML 데이터 형식 개요 XML과 문자열 형식 데이터의 점유 공간을 비교해보자. 예제 9 1 SELECT 2 DATALENGTH( 3 N'<?xml version="1.0" standalone="yes"?> 4 <bookstore> 5 <book category="CHILDREN"> 6 <title>Harry Potter</title> 7 <author>J K. Rowling</author> 8 <year>2005</year> 9 <price>29.99</price> 10 </book> 11 <book category="WEB"> 12 <title>Learning XML</title> 13 <author>Erik T. Ray</author> 14 <year>2003</year> 15 <price>39.95</price> 16 </book> 17 </bookstore>'

3. XML 데이터 형식 개요 18 ) AS NVARLEN, 19 DATALENGTH( 20 CAST(N'<?xml version="1.0“ standalone="yes"?> 21 <bookstore> 22 <book category="CHILDREN"> 23 <title>Harry Potter</title> 24 <author>J K. Rowling</author> 25 <year>2005</year> 26 <price>29.99</price> 27 </book> 28 <book category="WEB"> 29 <title>Learning XML</title> 30 <author>Erik T. Ray</author> 31 <year>2003</year> 32 <price>39.95</price> 33 </book> 34 </bookstore>' 35 AS XML) 36 ) AS XMLLEN;

3. XML 데이터 형식 개요

3. XML 데이터 형식 개요 xml 데이터 형식에는 다음과 같은 제한 사항이 적용된다. 데이터의 크기가 2GB를 초과할 수 없다. text 또는 ntext로의 캐스트 또는 변환을 지원하지 않고, 대신 varchar(max) 또는 nvarchar(max)를 사용해야 한다. 비교 또는 정렬할 수 없다. 즉 xml 데이터 형식은 GROUP BY 문에서 사용할 수 없다.

3. XML 데이터 형식 개요 ISNULL, COALESCE 및 DATALENGTH 이외의 기본 제공 스칼라 함수에 대한 매개 변수로 사용할 수 없다. 인덱스에서 키 열로 사용할 수 없다. 하지만 클러스터형 인덱스의 데이터로 포함하거나 INCLUDE 키워드를 사용하여 비클러스터형 인덱스에 명시적으로 추가할 수는 있다.

3. XML 데이터 형식 형식화되지 않은 XML 형식 XML 데이터 형식은 형식화되지 않은 XML과 형식화된 XML로 구분된다. 형식화되지 않은 XML 형식은 열, 변수, 매개변수 등에 사용될 수 있다. [예제 10]의 5행에서는 xmlCol 열이 형식화되지 않은 XML 형식으로 정의되었다. 즉, 단순히 데이터 형식 xml만을 지정했다 (뒤에 설명하겠지만, 형식화된 XML의 경우에는 이 xml 뒤에 부수적인 정의가 추가된다).

3. XML 데이터 형식 형식화되지 않은 XML 형식 XML 데이터 형식의 열이나 변수에 데이터를 저장하는 방법은 여러 가지가 있으나 가장 간단한 방법은 문자열로부터 암시적으로 변환하는 것이다. [예제 10]의 10~23행의 문자열 상수는 XML 데이터 형식 으로 암시적으로 변환되어 xmlCol 열에 저장된다. 10~23행의 XML 문서는 최상위 수준에 단일 요소(<bookstore>)가 있는 형태로 이것을 문서(document)라고 한다. <bookstore> 요소 가 없는 경우는 콘텐츠(content)라고 하는데, 이것도 XML 데이터 형식의 열에 저장할 수 있다.

3. XML 데이터 형식 형식화되지 않은 XML 형식 형식화되지 않은 XML 형식 열을 생성 또는 삽입해보자. 예제 10 1 USE Test1DB; 2 GO 3 CREATE TABLE untyped_xmltbl ( 4 pk int IDENTITY PRIMARY KEY 5 , xmlCol xml NOT NULL 6 ); 7 GO 8 INSERT untyped_xmltbl (xmlCol) 9 VALUES ( 10 '<bookstore> 11 <book category="CHILDREN"> 12 <title>Harry Potter</title> 13 <author>J K. Rowling</author>

3. XML 데이터 형식 형식화되지 않은 XML 형식 14 <year>2005</year> 15 <price>29.99</price> 16 </book> 17 <book category="WEB"> 18 <title>Learning XML</title> 19 <author>Erik T. Ray</author> 20 <year>2003</year> 21 <price>39.95</price> 22 </book> 23 </bookstore>'); 24 GO 25 INSERT untyped_xmltbl (xmlCol) 26 VALUES ( 27 '<bookstore> 28 <book category="CHILDREN">'); 29 GO 30 SELECT * FROM untyped_xmltbl;

3. XML 데이터 형식 형식화되지 않은 XML 형식

3. XML 데이터 형식 형식화되지 않은 XML 형식 XML 데이터를 명시적으로 변환해보자. 예제 11 1 USE Test1DB; 2 GO 3 INSERT untyped_xmltbl (xmlCol) 4 VALUES (CAST( 5 '<book category="CHILDREN"> 6 <title>Harry Potter</title> 7 <author>J K. Rowling</author> 8 <year>2005</year> 9 <price>29.99</price> 10 </book> 11 <book category="WEB"> 12 <title>Learning XML</title> 13 <author>Erik T. Ray</author> 14 <year>2003</year> 15 <price>39.95</price> 16 </book>' AS XML)); 17 GO 18 SELECT * FROM untyped_xmltbl;

3. XML 데이터 형식 형식화되지 않은 XML 형식

3. XML 데이터 형식 스키마 컬렉션 생성, 수정 및 삭제 스키마 컬렉션을 생성, 수정, 삭제하는 구문 스키마 컬렉션(schema collection): XML 데이터 형식의 열에 저장될 수 있는 XML 문서의 유효성 검사 제약조건 또는 데이터 형식 정보를 지정하는 것으로, 스키마 컬렉션이 바인딩된 XML을 형식화된 XML이라고 한다. 스키마 컬렉션을 생성, 수정, 삭제하는 구문

3. XML 데이터 형식 스키마 컬렉션 구문 설명 relational_schema : 관계형 스키마 이름으로, 이를 생략하면 기본 관계형 스키마(dbo)가 사용된다. sql_identifier : XML 스키마 컬렉션의 이름이다. Expression : 문자열 상수 또는 스칼라 변수로, varchar, varbinary, nvarchar 또는 xml 형식이다. Schema Component : 삽입할 스키마 구성 요소다.

3. XML 데이터 형식 스키마 컬렉션 스키마 컬렉션의 개념을 살펴보자. 예제 12 1 USE Test1DB; 2 GO 3 CREATE XML SCHEMA COLLECTION Test1Schema 4 AS 5 '<xsd:schema xmlns:xsd="http: //www.w3.org/2001/XMLSchema"> 6 <xsd:element name="name" type="xsd:string"/> 7 <xsd:element name="age" type="xsd:integer"/> 8 </xsd:schema>'; 9 GO 10 CREATE TABLE typed_xmltbl ( 11 pk int IDENTITY PRIMARY KEY

3. XML 데이터 형식 스키마 컬렉션 12 , xmlCol xml (Test1Schema) NOT NULL 13 ); 14 GO 15 INSERT typed_xmltbl (xmlCol) 16 VALUES ('<name>홍길동</name> <age>25</age>'); 17 GO 18 INSERT typed_xmltbl (xmlCol) 19 VALUES ('<name>을지매</name> <age> 서른</age>'); 20 GO 21 INSERT typed_xmltbl (xmlCol) 22 VALUES ('<nickname>박찬호</nickname> <age>32</age>'); 23 GO 24 SELECT * FROM typed_xmltbl;

3. XML 데이터 형식 스키마 컬렉션

3. XML 데이터 형식 스키마 컬렉션 기정의 스키마 [예제 12]에서 5행의“http://www.w3.org/2001/XMLSchema” 는 공용으로 사용되는 기정의(predefined) 스키마다. 이외에도 다음과 같은 기정의 스키마가 있다. 새로운 스키마를 정의할 때 xml, xs, xsi와 같은 기정의 스키마의 이름은 사용할 수 없다.

3. XML 데이터 형식 스키마 컬렉션 xml = http://www.w3.org/XML/1998/namespace xs = http://www.w3.org/2001/XMLSchema xsi = http://www.w3.org/2001/XMLSchema-instance fn = http://www.w3.org/2004/07/xpath-functions sqltypes = http://schemas.microsoft.com/ sqlserver/2004/sqltypes xdt = http://www.w3.org/2004/07/xpath-datatypes (no prefix) = urn:schemas-microsoft-com:xml-sql (no prefix) = http://schemas.microsoft.com/ sqlserver/2004/SOAP

3. XML 데이터 형식 스키마 컬렉션 예를 들어, sqltypes 스키마는 XML 데이터의 형식을 SQL 데이터 형식으로 매핑할 수 있도록 한다. 기정의 스키마 중 URL 형태로 되어 있는 것을 웹 브라우저에서 열어 보면 해당 스키마의 정의 또는 안내서를 볼 수 있다. 조회 스키마 컬렉션의 정보를 파악할 수 있도록 여러 종류의 카탈로그 뷰가 제공된다.

3. XML 데이터 형식 스키마 컬렉션 데이터베이스 내의 모든 스키마 컬렉션을 조회해보자. 예제 13 2행과 같이 sys.xml_schema_collections 카탈로그 뷰의 내용을 확인하면 됨 예제 13 1 USE AdventureWorksLT; 2 SELECT * FROM sys.xml_schema_collections;

3. XML 데이터 형식 스키마 컬렉션 스키마 컬렉션에서 모든 스키마 또는 특정 스키마의 내용을 조회 하려면 xml_schema_namespace 함수를 사용하면 된다. xml_schema_namespace의 구문 Relational_schema : 관계형 스키마 이름이다. XML_schema_collection_name : 조회할 XML 스키마 컬렉션의 이름이다. Namespace : 조회할 특정 XML 스키마의 네임스페이스 URI

3. XML 데이터 형식 스키마 컬렉션 ProductDescriptionSchemaCollection의 모든 스키마를 조회해보자. 예제 14 1 USE AdventureWorksLT; 2 SELECT xml_schema_namespace(N'SalesLT', N'ProductDescriptionSchemaCollection');

3. XML 데이터 형식 스키마 컬렉션

3. XML 데이터 형식 스키마 컬렉션

3. XML 데이터 형식 문서와 콘텐츠 문서(document)와 콘텐츠(content)에 대해 구체적으로 알아보자. 단일 요소만 허용하도록 열을 제한할 수 있는데, 이를 위해서는 [예제 11-15]의 5행처럼 스키마 컬렉션 이름 앞에‘DOCUMENT’를 지정하면 된다. 만약 이렇게 지정한 열에 9행과 같은 콘텐츠 데이터를 삽입하면 실패 하게 된다.

3. XML 데이터 형식 문서와 콘텐츠 데이터의 종류를 DOCUMENT로 잘못 지정한 예를 살펴보자. 예제 15 1 USE Test1DB; 2 GO 3 CREATE TABLE document_xmltbl ( 4 pk int IDENTITY PRIMARY KEY 5 , xmlCol xml (DOCUMENT Test1Schema) NOT NULL 6 ); 7 GO 8 INSERT document_xmltbl (xmlCol) 9 VALUES ('<name>을지매</name> <age>30</age>'); 10 GO 11 SELECT * FROM document_xmltbl;

3. XML 데이터 형식 문서와 콘텐츠

3. XML 데이터 형식 문서와 콘텐츠 콘텐츠에는 다음과 같은 특성이 있다. 0개 이상의 최상위 요소가 존재한다. 최상위 요소에 텍스트 노드가 올 수 있다. 즉 최상위 요소가 하나도 없을 수도 있고(최상위 요소에 텍스트 노드만 오는 경우), 1개만 있을 수도 있고(이때는 문서가 된다), 2개 이상이 있을 수도 있다.

3. XML 데이터 형식 문서와 콘텐츠 데이터의 종류를 CONTENT로 지정한 예를 살펴보자. 예제 16 1 USE Test1DB; 2 GO 3 CREATE TABLE content_xmltbl ( 4 pk int IDENTITY PRIMARY KEY 5 , xmlCol xml (CONTENT Test1Schema) NOT NULL 6 ); 7 GO 8 INSERT content_xmltbl (xmlCol) 9 VALUES ('<name>을지매</name> <age>30</age>'); 10 GO 11 SELECT * FROM content_xmltbl;

3. XML 데이터 형식 문서와 콘텐츠

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 XML 데이터를 조작하는 언어인 XQuery와 XQuery 내에서 사용 하는 함수 그리고 XML에서 사용하는 데이터의 형식들을 예를 들어 살펴보자. XQuery 관계형 데이터를 조작하는 언어가 SQL이라면 XML 데이터를 조작 하는 언어는 XQuery다. XQuery는 과거의 XPath보다 훨씬 포괄적이고 효율적인 언어다.

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 XQuery를 테스트하기 위한 행을 삽입해보자. 예제 17 XQuery를 테스트하기 위해 앞에서 만들었던 untyped_xmltbl 테이블에 한 행을 삽입하기로 함 5~33행의 XML 문서가 테스트의 대상이 될 것임 예제 17 1 USE Test1DB; 2 GO 3 INSERT untyped_xmltbl (xmlCol) 4 VALUES ( 5 '<bookstore> 6 <book category="CHILDREN"> 7 <title>Harry Potter</title>

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 8 <author> 9 <firstname>John</firstname> 10 <lastname>Rowling</lastname> 11 </author> 12 <year>2005</year> 13 <price>29.99</price> 14 </book> 15 <book category="WEB"> 16 <title>Learning XML</title> 17 <author> 18 <firstname>Erik</firstname> 19 <lastname>Ray</lastname> 20 </author> 21 <year>2003</year> 22 <price>39.95</price> 23 </book>

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 24 <book category="WEB"> 25 <title>Learning Web Design </title> 26 <author> 27 <firstname>Jennifer </firstname> 28 <lastname>Robbins</lastname> 29 </author> 30 <year>2007</year> 31 <price>29.69</price> 32 </book> 33 </bookstore>'); 34 GO 35 SELECT * 36 FROM untyped_xmltbl 37 WHERE pk = 4;

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 간단한 XPath 쿼리를 사용해보자. 예제 18 2행의 query는 XML 데이터 형식 메서드 중 하나로, XML 인스턴스에 대한 쿼리를 수행함 4행의‘/bookstore//title’는 XPath 쿼리로, 모든 bookstore 아래의 모든 요소 아래의 모든 title 요소를 조회하는 것임 - 실행 결과에 모든 title 요소들이 출력되는 것을 볼 수 있음 예제 18

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 1 USE Test1DB; 2 SELECT xmlCol.query( 3 '/bookstore//title') 4 FROM untyped_xmltbl 5 WHERE pk = 4;

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 FLWOR 전형적인 XQuery는 FLWOR 반복 구문으로 구성된다. FLWOR란 FOR, LET, WHERE, ORDER BY, RETURN을 나타내는 약어로, ‘flower’와 같이 발음된다. FLWOR 반복 구문은 다음과 같은 요소들로 구성된다. FOR 절 : 하나 이상의 반복기 변수를 입력 시퀀스로 바인딩하는데, 입력 시퀀스는 XPath 등의 XQuery 식이 될 수 있으며, 노드 시퀀스나 원자성 값의 시퀀스 중 하나다. 원자성 값 시퀀스는 상수나 생성자 함수를 사용하여 생성할 수 있다. SQL의 SELECT와 FROM 절에 대응된다.

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 LET 절 : 특정 반복에 대해 지정된 변수에 값을 할당한다. 할당된 식은 XPath 등의 XQuery 식일 수 있으며 노드 시퀀스나 원자성 값 시퀀스를 반환할 수 있다. 원자성 값 시퀀스는 상수나 생성자 함수를 사용하여 생성할 수 있다. WHERE 절 : 반복에 필터 조건자를 적용한다. SQL의 WHERE 절에 대응 된다. ORDER BY 절 : 결과 집합을 지정된 방법으로 정렬시켜준다. SQL ORDER BY 절에 대응된다. - RETURN 식 : FLWOR 문에서 반환할 결과를 구성한다.

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 간단한 FLWOR 문을 살펴보자. 예제 19 1 USE Test1DB; 2 SELECT xmlCol.query(' 3 for $b in //book 4 where $b/year = 2005 5 return $b/title 6 ') AS Result 7 FROM untyped_xmltbl 8 WHERE pk = 4;

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 XQuery 함수 예제 20 1 USE Test1DB; XQuery 함수 중에서 round와 avg 함수를 사용해보자. 예제 20 1 USE Test1DB; 2 SELECT xmlCol.query(' 3 round(avg(//price)) 4 ') AS Result 5 FROM untyped_xmltbl 6 WHERE pk = 4;

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 이밖에도 [표 11-2]와 같은 각종 함수들이 있다(각 함수에 대한 자세 한 설명은 온라인 도움말을 참고할 것).

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 기타 XML 데이터 형식 메서드 value( ) : XML로부터 스칼라 값을 반환한다. exist( ) : XML 문서에서 특정 XML이 존재하는지를 확인한다. nodes( ) : XML로부터 행 집합 표현을 반환한다.

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 value( ) 메서드를 사용해보자. 예제 21 1 USE Test1DB; 2 SELECT xmlCol.value( 3 '/bookstore[1]/book[2]/year[1][text()]', 4 'integer' 5 ) AS Result 6 FROM untyped_xmltbl 7 WHERE pk = 4;

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 exist( ) 메서드는 비어 있지 않은 결과를 반환하면 1을, 빈 결과를 반환하면 0을, 그리고 주어진 XML 인스턴스에 NULL이 있으면 NULL을 반환한다. exist( ) 메서드를 사용해보자. 예제 22 1 USE Test1DB; 2 SELECT pk 3 FROM untyped_xmltbl 4 WHERE xmlCol.exist( 5 '/bookstore/book/year[.="2003"]' 6 ) = 1; 7 SELECT pk 8 FROM untyped_xmltbl 9 WHERE xmlCol.exist( 10 '/bookstore/book/year[.="2004"]' 11 ) = 1;

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 nodes( ) 메서드는 쿼리가 반환하는 각 행에 대해 결과 집합을 반환한다. nodes( ) 메서드를 사용해보자. 예제 23 1 USE Test1DB; 2 SELECT node_tbl.author.query('.') AS Result 3 FROM untyped_xmltbl 4 CROSS APPLY xmlCol.nodes('/bookstore/ book/author') AS node_tbl (author);

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드

3. XML 데이터 형식 XQuery와 XML 데이터 형식 메서드 OUTER APPLY라는 연산자도 있는데, 이것은 결과 집합 생성과 무관하게 모든 untyped_xmltbl 테이블의 행들을 반환한다. 이밖에 modify( ) 메서드가 있는데, 이것으로는 XML 유형의 변수 또는 열의 내용을 수정할 수 있다. 즉 XML DML 문을 사용하여 XML 데이터에서 노드를 삽입, 갱신 또는 삭제한다.

3. XML 데이터 형식 기타 그 밖에, XML 데이터 형식과 관련하여 다음과 같은 고급 주제가 있다. 외래 키를 만들 수 없고 유일성 제약조건도 설정할 수 없다(그러나 INCLUDE 키워드를 사용하여 클러스트형 또는 비클러스트형 인덱스에 XML 열을 포함시킬 수는 있다). 그리고 XML 열에 대해 CHECK 제약조건을 설정할 때는 XML 데이터 형식 메서드를 직접 사용할 수 없으므로, 별도의 사용자 정의 함수를 정의하고 CHECK 제약조건에서 이 함수를 호출해야 한다.

3. XML 데이터 형식 기타 인덱싱 : XML 열에 저장되는 데이터가 크고(최대 2GB) 행 수가 많아질 경우, Xquery 등의 조작을 하는 데 많은 시간이 걸릴 수 있으며, 이 경우 에는 XML 열에 대한 인덱싱이 필요하다. 그러나 XML 열에 대한 인덱스는 일반 열과는 달리, 기본 XML 인덱스와 보조 XML 인덱스로 구분된다. XML 열의 첫 번째 인덱스는 기본 XML 인덱스여야 하며, 그 후에 PATH, VALUE 및 PROPERTY라는 세 종류의 보조 인덱스를 추가로 만들 수 있다. 이 보조 인덱스들은 쿼리 유형별로 성능을 향상시킬 수 있다.

3. XML 데이터 형식 기타 전체 텍스트 검색 : XML 열 내에 저장된 텍스트를 빨리 찾기 위해 전체 텍스트 검색(Full-Text Search)이 필요한 경우가 있다. 이를 위해서는 전체 텍스트 인덱스(Full-Text Index)를 설정해야 하는데, 일반 문자열 에 설정되는 전체 텍스트 인덱스와는 달리 XML 열의 경우에는 여러 가지 제한 사항이 적용된다.