XQuery를 사용한 XML 데이터 업데이트
- Version : SQL Server 2005, 2008, 2008R2, 2012
XML의 값을 수정하는 방법에는 무엇이 있을까? XML 편집기 등을 이용하여 사용자가 직접 수정 할 수도 있지만 XML노드와 값을 정확하게 수정하려면 XQuery를 사용하여 수정 할 수 있다.
XQuery는 구조화 되었거나 반구조화된 XML 데이터를 쿼리할 수 있는 언어이다. XQuery는 기존의 XPath 쿼리 언어를 기반으로 더 나은 반복 성능 및 정렬 결과를 위한 지원이 추가 되었다.
자세한 내용은 MSDN을 참고한다.
[노드 삽입]
삽입 키워드는 다음과 같은 구조를 사용한다.
Insert Expression1 ({as first | as last} into | after | before Expression2)
삽입 키워드를 통하여 샘플 테이블을 생성 한다.
CREATE TABLE HR_XML (ID INT IDENTITY, SALARIES XML) GO INSERT HR_XML VALUES( '<SALARIES> <MARKETING> <EMPLOYEE ID="1" TIER="4"> <SALARY>42000</SALARY> </EMPLOYEE> <EMPLOYEE ID="2" TIER="1"> <SALARY>52000</SALARY> </EMPLOYEE> <EMPLOYEE ID="3" TIER="4"> <SALARY>48000</SALARY> </EMPLOYEE> </MARKETING> </SALARIES> ' ) GO
SELECT * FROM HR_XML GO |
<Account / >라는 새로운 노드를 생성하려면 INSERT 구문을 이용하여 수행 할 수 있다.
UPDATE HR_XML SET Salaries.modify('insert <Accounting /> into (/Salaries)[1]') GO
SELECT * FROM HR_XML GO |
위의 쿼리에서 보면 Salaries [1] 노드에 대해 Singleton Designation 값이 지정된 것을 확인 할 수 있다. Singleton Designation 없이 쿼리를 실행하면 오류가 발생 한다.
UPDATE HR_XML SET Salaries.modify('insert <Accounting /> into (/Salaries)') GO |
[원하는 위치 노드 추가]
노드를 추가하면 기존의 노드 이후에 삽입되는 것을 확인 할 수 있다. (위 그림 참조). 원하는 위치에 노드를 삽입하는 방법을 알아 보자. 실습에서는 <Salaries> 아래 첫번째 노드에 삽입을 하여 보자. First into 명령어를 이용한다.
UPDATE HR_XML SET Salaries.modify('insert <Accounting /> as first into (/Salaries)[1]') GO
select * from HR_XML GO |
첫번째 노드 <Account />에 Employee 노드(ID, tier)를 입력 해보자. 노드를 입력 할 때 속성을 포함해야 한다. Singleton Designation [2] 지정으로 <Account>노드 바로 아래 <Employee>가 삽입 되는 것을 확인 할 수 있다.
UPDATE HR_XML SET Salaries.modify('insert <Employee ID="4" tier="4" /> into (/Salaries/Accounting)[1]') GO
select * from HR_XML GO |
[특정 노드를 식별하여 삽입]
특정 Employee의 노드에 새 노드를 삽입하여 보자. @ID 인수를 사용하여 Employee를 식별 할 수 있다. 실습에서는 Employee ID = 2인 노드를 찾아서 새로운 노드를 삽입하였다.
UPDATE HR_XML SET Salaries.modify('insert <Projects /> into (/Salaries/Marketing/Employee[@ID=("2")])[1]') GO
select * from HR_XML GO |
[노드 삽입과 값 삽입]
노드 삽입과 컬력션 삽입을 할 수 있다. Employee id =2의 노드에 새로운 노드(Project ID = 1)과 값을 삽입한다.
UPDATE HR_XML SET Salaries.modify('insert <Project ID="1"><Description>Organize new strategies</Description></Project> into (/Salaries/Marketing/Employee[@ID=("2")]/Projects)[1]') GO
select * from HR_XML GO |
[다른 노드 값을 참조하여 노드 삽입]
Employee id = 1의 값을 참조하여 Employee ID = 4의 <Salary>노드를 추가하여 보자. 중괄호를 사용하여 명시적으로 노드를 지정한 것을 확인 할 수 있다.
UPDATE HR_XML SET Salaries.modify('insert <Salary>{(/Salaries/Marketing/Employee[@ID=("1")]/Salary/text())}</Salary> into (/Salaries/Accounting/Employee[@ID=("4")])[1]') GO
select * from HR_XML GO |
[노드 삭제]
노드 삭제는 노드 삽입보다 간단하다. 표현식은
Delete Expression 이다. 노드 마지막의 <Account />노드를 삭제 하여 보자.
UPDATE HR_XML SET Salaries.modify('delete (/Salaries/Accounting)[2]') GO
select * from HR_XML GO |
[값 삭제]
값 삭제는 텍스트() 함수를 사용하여 삭제 할 수 있다.
UPDATE HR_XML SET Salaries.modify('delete (/Salaries/Marketing/Employee[@ID=("2")]/Projects/Project[@ID="1"]/Description/text())[1]') GO
select * from HR_XML GO |
[값 수정]
Exployee ID = 2의 <Salary> 값을 수정하여 보자.
UPDATE HR_XML SET Salaries.modify('replace value of (/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")') GO
select * from HR_XML GO |
[XQuery의 산술값을 이용한 수정]
모든 Exployee에 대해서 <Salary>의 값이 10% 증가한 값을 수정할 수 있도록 반복문을 사용 할 수 있다.
DECLARE @i INT = 1 WHILE @i <= 3 BEGIN UPDATE HR_XML SET Salaries.modify('replace value of (/Salaries/Marketing/Employee[@ID=(sql:variable("@i"))]/Salary/text())[1] with (/Salaries/Marketing/Employee[@ID=(sql:variable("@i"))]/Salary)[1] * 1.01') SET @i+=1 END GO
select * from HR_XML GO |
[수정 제한]
XQuery에서 Modify() 메소드는 수정하려는 값을 SELECT와 함께 사용 할 수 없다.
SELECT Salaries.modify('replace value of (/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")') FROM HR_XML GO |
기존의 값을 조회하여 수정하려면 다음과 같이 변수를 이용하여 사용하여야 한다.
DECLARE @x XML SELECT @x = Salaries FROM HR_XML SET @x.modify('replace value of (/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")') SELECT @x GO |
쿼리문에서 UPDATE 구문을 동시에 2개 사용할 수 없다. 별도의 업데이트문을 사용하여야 한다.
UPDATE HR_XML SET Salaries.modify('replace value of (/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")'), Salaries.modify('replace value of (/Salaries/Marketing/Employee[@ID=("1")]/Salary/text())[1] with ("60000")') GO |
Employee ID = 2의 값을 참조하여 Employee ID = 1의 값을 업데이트 할 수 있다.
UPDATE HR_XML SET Salaries.modify('replace value of (/Salaries/Marketing/Employee[@ID=("1")]/Salary/text())[1] with (/Salaries/Marketing/Employee[@ID=("2")]/Salary)') GO
select * from HR_XML GO |
[계층 값 변경]
@ID 값을 사용하여 Employee ID = 1의 tier = 4 값을 tier =1로 변경 할 수 있다.
UPDATE HR_XML SET Salaries.modify('replace value of (/Salaries/Marketing/Employee[@ID=("1")]/@tier)[1] with "1"') GO
select * from HR_XML GO |
XQuery를 사용하여 XML DML 실습을 하였다. 많은 XML 편지기가 있지만 SSMS를 이용하여 속성값을 사용하여 정확하게 수정할 때에 매우 유용하게 사용 할 수 있을 듯 하다.
[참고 자료]
http://msdn.microsoft.com/ko-kr/library/ms189075.aspx
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
