MSSQL 에서 XML 데이터 입력, 출력, 수정하는 방법

DML(DML Usage on an untyped XML column) 이라고 불리는 데이터 데이터 조작 처리 언어를 통해 우리는 데이터를 조회(SELECT)하고, 저장하고(INSERT)하고, 수정하고(UPDATE), 삭제(DELETE) 할 수 있습니다. SQL Server 2005 에서는 XML 문서를 처리하귀 위한 XML 이라는 새로운 데이터 형식이 제공되는데, XML 형식의 열의 값 또한 DML 을 이용하여 조회하거나 삭제, 수정, 입력 할 수 있습니다.

이 문서에서는 XML 형식의 열의 데이터를 찾고, 수정하고, 입력하고, 삭제하는 방법을 설명할 것입니다.

Step 1

다음의 쿼리를 실행하여 [test]라는 데이터베이스를 생성하고, [MyXMLtable] 테이블을 생성합니다. [MyXMLtable] 테이블은 XML 형식의 열을 포함하여 실제 XML 형식의 문서가 들어가게 됩니다.

USE [master]
GO
/****** Object:  Database [test]    Script Date: 08/24/2006 14:50:29 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'test')
DROP DATABASE [test]
GO
CREATE DATABASE test
GO
USE [test]
GO
/****** Object:  Table [dbo].[MyXMLTable]    Script Date: 08/24/2006 14:50:14 ******/
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[MyXMLTable]') AND type IN (N'U'))
DROP TABLE [dbo].[MyXMLTable]
GO
CREATE TABLE MyXMLTable (id INT, xmldat XML)
GO
INSERT INTO MyXMLTable SELECT 1,'
<?Names of Cat?> 
<Root>
 <!-- How to name your cat -->
<Cats> 
 <AmericanShorthair color="Grey" Friendly="No">Tiger</AmericanShorthair>
 <PersianHimalayan>MeowMeow</PersianHimalayan>
 <JapaneseBobtail>Rainbow River</JapaneseBobtail>
</Cats>
</Root>
'
GO
INSERT INTO MyXMLTable SELECT 2,'
<?Names of Dog?> 
<Root>
 <!-- How to name your Dog -->
<Dog> 
 <GreyHound color="dottedwhite" Intelligence="None">oldie</GreyHound>
 <Pomeranian>Jackie</Pomeranian>
 <ShihTzu>Rainbow</ShihTzu>
</Dog>
</Root>
'
GO  

 

Step 2

[id] 열의 값이 2인 행의 XML 문서에서, Dogs 요소의 모든 내용을 출력하기 위해서는 다음의 쿼리를 사용합니다.

USE test
GO
SELECT xmldat.query(' //Dog') FROM MyXMLTable  WHERE id=2  

위의 쿼리는 아래와 같은 결과를 보여주게 됩니다.

<Dog><GreyHound color="dottedwhite" Intelligence="None">oldie</GreyHound>
   <Pomeranian>Jackie</Pomeranian><ShihTzu>Rainbow</ShihTzu></Dog>

이번에는 Cats 요소의 하위에 있는 PersianHimalayan 요소의 모든 내용을 출력해 보겠습니다. 다음의 쿼리를 실행해 주십시오.

USE test
GO
SELECT xmldat.query(' //Cats/PersianHimalayan') FROM MyXMLTable  WHERE id=1

위 쿼리는 다음과 같은 결과를 보여줍니다.

<PersianHimalayan>MeowMeow</PersianHimalayan>

Step 3

이번에는 id 열의 값이 1인 행의 XML 데이터에서 PersianHimalayan 요소의 내용을 삭제해 보겠습니다. XML 형식의 열의 데이터를 수정하기 위해서는 UPDATE 쿼리와 함께 특별한 문장을 함께 적어주어야 합니다. 바로 열이름.modify 인데, 이 부분을 UPDATE 쿼리와 함께 지정합니다.

USE test
GO
UPDATE MyXMLTable 
SET xmldat.modify('delete /Root/Cats/PersianHimalayan ') WHERE id =1  

위의 쿼리는 루트 요소 하위의 Cats 요소 하위 요소중 PersianHimalayan를 삭제하는 내용입니다. 위의 쿼리를 실행하고 다시 PersianHimalayan 요소를 출력하는 쿼리를 실행해 보겠습니다.

USE test
GO
SELECT xmldat.query(' //Cats/PersianHimalayan') FROM MyXMLTable  WHERE id=1

위의 쿼리를 실행하면 PersianHimalayan 요소를 삭제하였기 때문에, 아무런 결과도 출력하지 않습니다. 또한 다음의 쿼리를 실행하여 Cats 요소의 모든 내용을 출력하여 PersianHimalayan 요소의 존재를 확인할 수 있습니다.

USE test
GO
SELECT xmldat.query(' //Cats') FROM MyXMLTable  WHERE id=1  

결과는 아래와 같습니다.

<Cats><AmericanShorthair color="Grey" Friendly="No">Tiger</AmericanShorthair><JapaneseBobtail>Rainbow River</JapaneseBobtail></Cats>

 

Step 4

이번에는 Dog 요소 하위에  "Mayliah" 라는 값을 가지는 Dalmation 요소 만드는 예제를 살펴보겠습니다.

USE test
GO
UPDATE MyXMLTable 
SET xmldat.modify('insert <Dalmation>Mayliah</Dalmation> 
as first  
into   
(/Root/Dog)[1]') WHERE id =2  

위에서 새로 추가한 요소를 확인하기 위해서 Dog 요소 전체의 내용을 살펴보겠습니다.

USE test
GO
SELECT xmldat.query('//Dog') FROM MyXMLTable  WHERE id=2  

위 쿼리의 결과는 아래와 같이 Dog 요소 하위에 가장 먼저 새로운 내용이 들어가 있습니다.

<Dog><Dalmation>Mayliah</Dalmation><GreyHound color="dottedwhite" Intelligence="None">oldie</GreyHound>
   <Pomeranian>Jackie</Pomeranian><ShihTzu>Rainbow</ShihTzu></Dog>

이제 Cats 요소 하위에 존재하는 JapaneseBobtail 요소 바로앞에 새로운 요소를 삽입해 보겠습니다. 이번에 추가할 요소의 이름은 Oriental 이고 그 값은 "Serena" 을 가집니다.

USE test
GO
UPDATE MyXMLTable 
SET xmldat.modify('insert <Oriental>Serena</Oriental> 
before (/Root/Cats/JapaneseBobtail)[1] ') WHERE id = 1  

위에서 새로 삽입한 요소의 확인을 위해 아래 쿼리를 실행합니다.

USE test
GO
SELECT xmldat.query(' //Cats') FROM MyXMLTable  WHERE id = 1  

실행결과는 다음과 같습니다.

<Cats><AmericanShorthair color="Grey" Friendly="No">Tiger</AmericanShorthair>
   <Oriental>Serena</Oriental><JapaneseBobtail>Rainbow River</JapaneseBobtail></Cats>

Step 5

지금까지 우리는 XML 데이터를 삽입하고 삭제하는 방법을 살펴보았습니다.  데이터의 수정은 삭제와 삽입 작업의 조합으로 가능한데, 이는 DML 트리거나 같은 것입니다. Microsoft에서는 또한 replace 문장을 통해 데이터를 수정하는 방법을 제공하고 있습니다.

이제 AmericanShortHair 요소의 Friendly 속성의 값을 "No"에서 "Yes"로 변경하는 예제를 살펴보겠습니다.

USE test
GO
UPDATE MyXMLTable 
SET xmldat.modify('
  replace value of (/Root/Cats/AmericanShorthair[@color="Grey"]/@Friendly)[1]
  with "Yes" ') WHERE id = 1   

아래 쿼리를 실행하여 성공적으로 데이터가 수정되었는지 살펴보겠습니다.

USE test
GO
SELECT xmldat.query(' //Cats') FROM MyXMLTable  WHERE id=1  

실행 결과는 아래와 같습니다.

<PRE><Cats><AmericanShorthair color="Grey" Friendly="Yes">Tiger</AmericanShorthair><Oriental>Serena</Oriental><JapaneseBobtail>Rainbow River</JapaneseBobtail></Cats></PRE><PRE> </PRE>

영어 원문 : http://www.databasejournal.com/features/mssql/article.php/3629426

[출처] MSSQL 에서 XML 데이터 입력, 출력, 수정하는 방법|작성자 필립박