데이터베이스 개발자 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
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1067
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 960
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3127
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1472
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1233
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 938
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 848
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 913
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 948
» XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1893
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1257
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2693
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1690
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1764
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5703
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1348
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3838
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1795





XE Login