1.XML의 기본
(1)XML의 기본구조
-XML은 HTML과 같은 마크업언어의 한 종류이며 데이터를 어떻게 표현할 지를 나타내는 방식
-XML은 운영체제나 하드웨어 플랫폼,프로그래밍 언어에 종속되지 않는 중립적인 위치를 하게된다.
-다양한 운영체제와 어플리케이션 간 데이터를 주고 받는 역할
-HTML과 비슷하지만 , 훨씬 엄격한 문법을 지켜서 작성된다.
(2)간단한 XML의 예
<?XML version ="1.0" ?>
<document>
<userTbl name = "안정환" birthYear = 1979" addr ="강원" />
<userTbl name = "최진철" birthYear = 1975" addr ="제주" />
<userTbl name = "조재진" birthYear = 1986" addr ="충북" />
</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 * FROM OPENXML(@handle, '/root/userTbl',0)
WITH (userId nchar(8),
name nvarchar(10),
addr nchar(4) ); -- 표 형태로 데이터 처리
EXEC sp_xml_removedocument @handle; -- 메모리 해제