MS SQL 에서 XML

DataBase/SQL Server

by 탑~! 2010. 9. 16. 14:53

1.XML의 기본

(1)XML의 기본구조

-XML은 HTML과 같은 마크업언어의 한 종류이며
데이터를 어떻게 표현할 지를 나타내는 방식

-XML은 운영체제나 하드웨어 플랫폼,프로그래밍 언어에
종속되지 않는 중립적인 위치를 하게된다.

-다양한 운영체제와 어플리케이션 간 데이터를 주고 받는 역할

-HTML과 비슷하지만 , 훨씬 엄격한 문법을 지켜서
작성된다.

(2)간단한 XML의 예

<?XML version ="1.0" ?>

</document>

(3)XML데이터 형식

-SQL2005부터 XML데이터형식을 지원한다

-이전 버전에서는 TEXT와 VARCHAR형식을 사용했다.

2.XML의 활용

(1)XML의 기본적인 사용

USE master ;

RESTORE DATABASE sqlDB FROM DISK ='c:\sqldb.bak' WITH REPLACE ;

USE sqlDB;

//테이블 생성

CREATE TABLE xmlTbl (id INT IDENTITY PRIMARY KEY, xmlCol XML);

//XML형식으로는 일반텍스트 , HTML형식도 입력가능

INSERT INTO xmlTbl VALUES(N'일반 텍스트 입력');

INSERT INTO xmlTbl VALUES(N'<html> <body>
<b> 일반 텍스트 입력</b> </body>
</html>' );

//형식이 어긋난 태그는 입력이 되지 않는다.

INSERT INTO xmlTbl VALUES(N'<html> <body>
<b> 일반텍스트입력</body> </html>' );

INSERT INTO xmlTbl VALUES(N'<?xml
version="1.0" ?>

<document>

<userTbl name="안정환"
birthYear="1979" addr="강원 " />

<userTbl name="최진철"
birthYear="1975" addr="제주 " />

<userTbl name="조재진"
birthYear="1986" addr="충북 " />

<userTbl name="김남일"
birthYear="1977" addr="경북 " />

</document>

');

//일부 XML데이터도 입력이 가능하다.

INSERT INTO xmlTbl VALUES(N'

<userTbl name="안정환"
birthYear="1979" addr="강원 " />

<userTbl name="최진철"
birthYear="1975" addr="제주 " />

');

//XML데이터형 사용

DECLARE @x XML

SET @x = N'<userTbl name="안정환"
birthYear="1979" addr="강원 " />'

PRINT CAST(@x AS NVARCHAR(MAX))

(2)형식화된 XML과 XML스키마

-형식화된 XML을 사용하려면 XML스키마를 지정해야한다.(XML의 형식을 강화한 것이다)

-XML스키마란 XML데이터를 저장할 형식을 규정한
데이터를 말한다.

//XML스키마 조회

SELECT * FROM userTbl FOR XML RAW, ELEMENTS, XMLSCHEMA

//XML스키마 생성

CREATE XML SCHEMA
COLLECTION schema_userTbl AS '

<xsd:schema
targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
elementFormDefault="qualified">

<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"
/>

<xsd:element name="row">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="userID">

<xsd:simpleType>

<xsd:restriction
base="sqltypes:nchar" sqltypes:localeId="1042"
sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType
IgnoreWidth">

<xsd:maxLength value="8" />

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

<xsd:element name="name">

<xsd:simpleType>

<xsd:restriction base="sqltypes:nvarchar"
sqltypes:localeId="1042" sqltypes:sqlCompareOptions="IgnoreCase
IgnoreKanaType IgnoreWidth">

<xsd:maxLength value="10" />

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

<xsd:element name="birthYear"
type="sqltypes:int" />

<xsd:element
name="addr">

<xsd:simpleType>

<xsd:restriction base="sqltypes:nchar" sqltypes:localeId="1042"
sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType
IgnoreWidth">

<xsd:maxLength value="4" />

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

<xsd:element name="mobile1" minOccurs="0">

<xsd:simpleType>

<xsd:restriction base="sqltypes:nchar"
sqltypes:localeId="1042" sqltypes:sqlCompareOptions="IgnoreCase
IgnoreKanaType IgnoreWidth">

<xsd:maxLength value="3" />

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

<xsd:element name="mobile2"
minOccurs="0">

<xsd:simpleType>

<xsd:restriction base="sqltypes:nchar"
sqltypes:localeId="1042" sqltypes:sqlCompareOptions="IgnoreCase
IgnoreKanaType IgnoreWidth">

<xsd:maxLength value="8" />

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

<xsd:element name="height"
type="sqltypes:smallint" minOccurs="0" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

'

USE sqlDB;

//테이블생성(XML스키마를 따르는 XML데이터형 컬럼 생성)

CREATE TABLE tXmlTbl (id INT IDENTITY,
xmlCol XML (schema_userTbl) );

//스키마에 맞는 않는 형식 입력이 되지 않는다.

INSERT INTO tXmlTbl VALUES(N'일반 텍스트 입력');

//형식에 맞는 데이터 - 입력된다.

INSERT INTO tXmlTbl VALUES(N'

<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2">

<userID>AJH </userID>

<name>안정환</name>

<birthYear>1979</birthYear>

<addr>강원 </addr>

<height>182</height>

</row>

');

//컬럼순서가 바뀌면 입력이 안된다.

INSERT INTO tXmlTbl VALUES(N'

<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2">

<name>박지성</name>

<userID>PJS </userID>

<birthYear>1983</birthYear>

<addr>서울 </addr>

<mobile1>011</mobile1>

<mobile2>1111111 </mobile2>

<height>181</height>

</row>

');

//스키마스키마형 XML데이터형으로 SQL프로그래밍

DECLARE @tx XML(schema_userTbl)

SET @tx = N'

<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2">

<userID>CJC </userID>

<name>최진철</name>

<birthYear>1975</birthYear>

<addr>제주 </addr>

<mobile1>019</mobile1>

<mobile2>0000000 </mobile2>

<height>185</height>

</row>'

INSERT INTO tXmlTbl VALUES(@tx)

SELECT * FROM sys.xml_schema_collections;

//XML스키마의 정의 조회

SELECT XML_SCHEMA_NAMESPACE(N'dbo', N'schema_userTbl');

//XML스키마 삭제 - 삭제하려면 스키마를 사용하는 테이블을
먼저 삭제해야한다.

DROP XML SCHEMA COLLECTION schema_userTbl

(3)XML인덱스

-다른 데이터 형식과 마찬가지로 XML형식에도 인덱스를
사용할 수 있다.

-XML인덱스는 기본XML인덱스와 보조XML인덱스로 나뉜다.

-보조XML인덱스는 기본XML인덱스가 존재해야 생성할 수 있고 , 기본XML인덱스가 사라지면 보조XML인덱스도 함께 사라진다.

USE sqldb;

//쿼리문을 XML데이터로 변환해서 출력한다.

SELECT Title, FirstName, MiddleName, LastName, EmailAddress, Phone

FROM AdventureWorks.Person.Contact

WHERE contactid = 1

FOR
XML RAW

//테이블생성

CREATE TABLE indexXmlTbl (id INT IDENTITY NOT NULL PRIMARY
KEY,

fullName VARCHAR(30), xmlInfo XML);

//위의 테이블에 이름과 xml정보를 삽입한다.

DECLARE @xml XML -- XML 데이터

DECLARE @fullName VARCHAR(20)

DECLARE @contactId INT -- ContactID 변수

DECLARE @i INT -- 반복할 변수

DECLARE @cnt INT -- 전체 행 개수

SET @i = 1;

SET @contactId = 1;

SELECT @cnt = COUNT(contactID) FROM AdventureWorks.Person.Contact; -- 행 데이터
개수

WHILE (@i <= @cnt) -- 행데이터 개수 만큼

BEGIN

SET @fullName = (SELECT FirstName + ' ' + LastName

FROM AdventureWorks.Person.Contact

WHERE contactID = @contactId )

IF (@fullName <> '') -- 해당 contactId의
사용자가 있다면..

BEGIN

SET @xml = (SELECT Title, FirstName, MiddleName, LastName,
EmailAddress, Phone

FROM AdventureWorks.Person.Contact

WHERE contactID = @contactId

FOR XML RAW )

INSERT INTO indexXmlTbl VALUES (@fullName, @xml);

SET @i = @i + 1

END

SET @contactId = @contactId + 1

END

SELECT * FROM indexXmlTbl;

//위 테이블의 값으로 새로운 테이블 noIndexXmlTbl 생성

SELECT * INTO noIndexXmlTbl FROM indexXmlTbl;

//xml인덱스 생성

CREATE PRIMARY XML INDEX
xmlIdx_indexXmlTbl_xmlInfo

ON
indexXmlTbl (xmlInfo);

//전화번호가 334-555-0137인
사람에 대한 정보 조회 ( 인덱스가 사용하는 경우)

SELECT * FROM indexXmlTbl

WHERE xmlInfo.exist('/row[@Phone="334-555-0137"]')
= 1;

//전화번호가 334-555-0137인
사람에 대한 정보 조회 ( xml인덱스를 사용하지 않는 경우)

SELECT * FROM noIndexXmlTbl

WHERE xmlInfo.exist('/row[@Phone="334-555-0137"]') = 1;

//인덱스를 사용하는 경우가 위의 경우에는 다섯 배가량 빠르다.

(4)쿼리의 결과를 XML로(FOR
XML절)

USE sqlDB;

SELECT userId,name,addr,height FROM userTbl

WHERE height > 180

FOR XML RAW;//요소하나에
하나의 행을 표현하는 방식

SELECT userId,name,addr,height FROM userTbl

WHERE height > 180

FOR XML RAW ('회원');

//최상위요소를 덧붙여서 출력한다.

SELECT userId,name,addr,height FROM userTbl

WHERE height > 180

FOR
XML RAW ('회원') , ROOT;

//하위요소로 출력한다.

SELECT userId,name,addr,height FROM userTbl

WHERE height > 180

FOR
XML RAW , ELEMENTS;

//XMLDATA 형태로 출력한다.

SELECT userId,name,addr,height FROM userTbl

WHERE height > 180

FOR
XML RAW , XMLDATA;

//XMLSCHEMA형태로 출력한다.

SELECT userId,name,addr,height FROM userTbl

WHERE height > 180

FOR
XML RAW , XMLSCHEMA;

//AUTO모드로 출력 - RAW에서는 요소이름을 row라고 하는 대신에 테이블이름으로 사용하는 것

//테이블이름출력이 row대신 출력된다.

SELECT userId,name,addr,height FROM userTbl

WHERE height > 180

FOR
XML AUTO;

SELECT userId,name,addr,mobile1, mobile2 FROM userTbl

WHERE height > 180

FOR
XML AUTO, ELEMENTS;

//NULL인 컬럼의 값도 출력되도록 한다

SELECT userId,name,addr,mobile1, mobile2 FROM userTbl

WHERE height > 180

FOR
XML AUTO, ELEMENTS XSINIL;

(5)XML데이터를 일반쿼리의 결과로 만들기(OPENXML)

DECLARE @handle INT -- 핸들

DECLARE @xmlDoc XML -- XML 데이터

SET @xmlDoc = (SELECT userId,name,addr FROM userTbl

WHERE height > 180

FOR XML AUTO , ROOT); -- xml 요소 형태로 변환

//XML구문 분석

EXEC sp_xml_preparedocument @handle OUTPUT,
@xmlDoc; -- XML의 구문을 분석

// 최상위가 root이고 요소가 userTbl이다.0이나 1이면 속성중심매핑 , 2이면 요소중심매핑

//xml데이터형을 표형식으로 출력한다.

SELECT

출처 : http://blog.naver.com/99jack?Redirect=Log&logNo=130032206325

'DataBase > SQL Server' 카테고리의 다른 글

3자리 마다 콤마(,) 찍기 (0)	2010.10.22
저장 프로시저 성능추적 (0)	2010.10.15
MS SQL 에서 CURSOR 사용하기3 (0)	2010.09.16
MS SQL 에서 CURSOR 사용하기 2 (0)	2010.09.16
MS SQL 에서 CURSOR 사용하기1 (0)	2010.09.16