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

Presentation on theme: "SQL Server 2005 에서의 XML 지원 강사 : 정 홍 주 ㈜웹타임 수석 컨설턴트."— Presentation transcript:

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

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

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

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

5 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

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

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

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

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

10 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 뷰에서 확인

11 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

12 데모 : XML 데이터 형식  XML Column  Untyped XML  XML Index

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

14 XML Schema

15 XML Schema 생성  XML SCHEMA COLLECTION 생성 –XML 인스턴스와 관련되는 collection 이름을 지정 CREATE XML SCHEMA COLLECTION cvSchemas AS N' <xsd:schema targetNamespace=http://www.wtime.net/edu

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

17 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( ' 10 20 ') -- 실패 INSERT INTO xsd_tab VALUES( ' 10 foo ')

18 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"]')

19 XML Schema 제한사항  는 이미 있는 collection 요구 –schemaLocation 무시 ,,  BOL 참조

20 데모 : XML Schema  Typed XML

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

22 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

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

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

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

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

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

28 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, ' ') -- fails, no persons insert xmlpeople values( 2, ' ') Select pdoc From xmlpeople Where pdoc.exist(‘('/people/person’)=1

29 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

30 xml.modify -- modify 이용 서브 요소를 추가 SET @x.modify( 'insert 2002-06-15 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" ')

31 데모 : XQuery  XQuery Method

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