데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

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

No. Subject Author Date Views
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1458
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1227
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1087
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 927
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 910
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 940
» XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1853
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1250
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1221
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2638
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1680
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1701
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5580
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1336
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3643
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1743
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1239
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1254
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 862





XE Login