SQL Server 2005 에서의 XML 지원

SQL Server 2005에서의 XML지원 강사: 정 홍 주 ㈜웹타임 수석 컨설턴트

목차 • SQL Server 2005에서의 XML 개요 • XML 데이터 형식 • XML Index • XML Schema • XQuery

SQL Server 2005에서의 XML 개요 • 4 ’X’ • XML 데이터 형식 • XML Index • XML Schema Collection • XML Manipulation

XML 데이터 형식 • XML 데이터 형식-native 데이터 베이스 형식 • 테이블의 컬럼 • 저장 프로시저의 매개변수 • 사용자 정의 함수에서 리턴값 • 변수

XML 데이터 형식 CREATE TABLE xml_tab ( the_id INTEGER, xml_col XML) CREATE PROCEDURE transform ( @x XML, @y XML OUTPUT) AS ... CREATE FUNCTION simple ( @x NVARCHAR(max)) RETURNS XML AS DECLARE @a XML SET @a = @x ... RETURN @a

XML 데이터 형식 제한 사항 • comparison 지원 안됨(NULL 제외) • no equality comparison • no ORDER BY, GROUP BY • no built-in functions (ISNULL, COALESCE 제외) • KEY 열로 사용할 수 없음 • UNIQUE 제약으로 사용할 수 없음 • COLLATE으로 선언할 수 없음 • XML encoding 사용 • 그렇지 않으면 UNICODE로 저장

XML Column • XML Column은 well-formed XML 저장 가능 • XML 1.0 권고 • 문서 또는 단편

XML Column • XML Column 은 단순 TEXT 열이 아님 • XML 관련 기술 지원 • XML Schema • XML Index • XQuery 와 Xpath 2.0 • FOR XML, OpenXML

XML Column CREATE TABLE xml_tab ( the_id INTEGER, xml_col XML) GO -- 자동 변환 INSERT INTO xml_tab VALUES(1, '<doc/>') INSERT INTO xml_tab VALUES(2, N'<doc/>') SELECT CAST(xml_col AS VARCHAR(MAX)) FROM xml_tab WHERE the_id < 10 -- 에러, not well formed INSERT INTO xml_tab VALUES(3, '<doc><x1><x2></x1></x2></doc>')

XML Index • XML Column에 XML Index 생성 • XML 쿼리 최적화 • Primary xml index 제일 먼저 생성 • 세가지 특별한 인덱스 • VALUE –콘텐트 쿼리에 최적화 • PATH –구조적 쿼리에 최적화 • PROPERTY –이름/값 쌍에 최적화 CREATE TABLE xml_tab ( id integer primary key, doc xml) GO CREATE PRIMARY XML INDEX xml_idx on xml_tab (doc) GO sys.xml_indexes 뷰에서 확인

XML Index • 요구사항 • 테이블은 primary key를 가지고 있어야 함 • Composite XML INDEX는 허용 안됨 • Primary xml index 생성 후 추가 XML Index 생성 • XML Schema 를 이용 최적화 CREATE XML INDEX invpathidx ON xml_tab(invoice) USING XML INDEX xml_idx FOR PATH

데모 : XML 데이터 형식 • XML Column • Untyped XML • XML Index

목차 • SQL Server 2005에서의 XML 개요 • XML 데이터 형식 • XML Index • XMLSchema • XQuery

XML Schema <?xml version="1.0" encoding="UTF-8"?> <xsd:schema targetNamespace="http://www.wtime.net/edu" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http:// www.wtime.net/edu" elementFormDefault="qualified" version="2.05"> <xsd:import namespace="http://www.w3.org/1999/xlink" schemaLocation="xlinks.xsd"/> <xsd:include schemaLocation=“hongju.xsd"/> <xsd:complexType name="GeocodeQOSType"> <xsd:attribute name="accuracy" type="xsd:decimal" use="required"/> <xsd:attribute name="matchType" type="GeocodeMatchTypeType" use="required"/> </xsd:complexType> <xsd:element name="GeocodeQOS" type="GeocodeQOSType"/> <xsd:complexType name="GeocodeRequestType"> <xsd:complexContent> <xsd:extension base="AbstractRequestParametersType">

XML Schema 생성 • XML SCHEMA COLLECTION 생성 • XML 인스턴스와 관련되는 collection 이름을 지정 CREATE XML SCHEMA COLLECTION cvSchemas AS N'<?xml version="1.0" ?> <xsd:schema targetNamespace=http://www.wtime.net/edu

XML Schema 참조 • XML 데이터 형식이 사용하는 스키마는 데이터베이스에 있어야 함 CREATE TABLE Locations ( location xml(cvSchemas) ... )

Typed XML CREATE TABLE xsd_tab( id int IDENTITY primary key, -- namespace‘http://www.wtime.net/edu’ xsd_col xml(CONTENT, cvSchemas)) GO INSERT INTO xsd_tab VALUES( '<PSI xmlns=" http://www.wtime.net/edu "><P>10</P><S>20</S></PSI>') -- 실패 INSERT INTO xsd_tab VALUES( '<PSI xmlns=" http://www.wtime.net/edu "><P>10</P><S>foo</S></PSI>')

Xml Schema 정보 • xml_schema_namespace 이용 • XML schema collection 이름 • query 이용 하나의 namespace 결과 반환 • xml 형식 반환 SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'cvSchemas') go SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'cvSchemas').query('/xs:schema[@targetNamespace="http://www.wtime.net/edu"]')

XML Schema 제한사항 • <xs:import> 는 이미 있는 collection 요구 • schemaLocation 무시 • <xs:include>, <xs:redefine>, <xs:notation> • BOL 참조

데모 : XML Schema • Typed XML

목차 • SQL Server 2005에서의 XML 개요 • XML 데이터 형식 • XML Index • XML Schema • XQuery

XQuery • XQuery는 XML 과 데이터 베이스를 위한 표준언어 • XQuery는 XPath의 Superset • XPath 또는 FLWOR 표현식 사용 • 거의 모든 유효한 XPath 문장은 또한 XQuery • XPath 는 FLWOR 표현식에서 값을 선택하는데 사용 (: 유효한 XQuery :) /people/person[age > 30] (: FLWOR 표현식 :) for $p in /people/person where $p/age > 30 order by $p/age[1] return $p/name

XQuery • XQuery 메서드 • xml.query - XML instance 반환 • xml.value – scalar 반환 • xml.exist – bool 반환 • xml.nodes – 하나의 컬럼 rowset • xml.modify – instance 변경

FLWOR 표현식 • XML 쿼리는FLWOR 표현식 • 다섯가지 구문 형식으로 구성 • FOR • LET (SQL Server 2005 나System.Xml에서는 지원안됨) • WHERE • ORDER BY • RETURN

FLWOR for $p in people/person where $p> 30 return $p/name/givenName/text() MartinSimon

xml.query • xml.query는 XML 데이터 형식 인스턴스 반환 • XML 형식 인스턴스와 XQuery 입력 • XML 단편같은 스칼라 형식 반환 • 새로운 데이터를 형성하기 위해 constructor 이용 <Invoice xmlns=“http://witmens> <LineItem>Soda</LineItem> <LineItem>Ice</LineItem> </Invoice> SELECT invoice.query('declare namespace hong = "http://wtimens“ /hong:Invoice/hong:LineItem') FROM Invoices -- 결과 -------------------------- <LineItem>Soda</LineItem><LineItem>Ice</LineItem>

xml.value -- 어떤 행을 추가 INSERT xml_tab VALUES('<people><person name="curly"/></people>') INSERT xml_tab VALUES('<people><person name="larry"/></people>') INSERT xml_tab VALUES('<people><person name="moe"/></people>') -- 검색 쿼리 SELECT id, xml_col.value('/people/person/@name','varchar(50)') AS name FROM xml_tab -- 결과셋 id name -------------------------- 1 curly 2 larry 3 moe

xml.exist -- people root 밑에 person을 반드시 가지고 있어야 함. CREATE TABLE xmltab( id INTEGER PRIMARY KEY, pdoc XML CHECK (pdoc.exist('/people/person')=1) ) -- ok insert xmltab values( 1, '<people><person name="bob"/></people>') -- fails, no persons insert xmlpeople values( 2, '<people><emp name="fred"/></people>') Select pdoc From xmlpeople Where pdoc.exist(‘('/people/person’)=1

xml.nodes SELECT nCol.value('../../@InvoiceNo[1]', 'int') InvoiceNo, nCol.value('@Product[1]', 'int') ProductID, nCol.value('@Price[1]', 'money') Price, nCol.value('@Quantity[1]', 'int') Quantity FROM #Stores CROSS APPLY Invoices.nodes('declare default namespace="http://schemas.adventure-works.com/Invoices" /InvoiceList/Invoice/Items/Item') AS nTable(nCol) ORDER BY InvoiceNo

xml.modify -- modify 이용 서브 요소를 추가 SET @x.modify( 'insert <InvoiceDate>2002-06-15</InvoiceDate> into /Invoice[1] ') -- 또는특성을 추가 SET @x.modify('insert attribute status{"backorder"} into /Invoice[1] ') -- 모든 LineItem 요소를 삭제 SET @x.modify('delete /Invoice/LineItems/LineItem') -- CustomerName 요소의 값을 변경 SET @x.modify('replace value of /Invoice[1]/CustomerName[1]/text()[1] with "John Smith" ')

데모 : XQuery • XQuery Method

STEP 요약 • 4 ’X’ • XML 데이터 형식 • XML Index • XML Schema Collection • XQuery

SQL Server 2005 에서의 XML 지원