'XQuery' 태그의 글 목록

XQuery

XQuery 관련 튜토리얼

중년 플머 김씨 2015. 1. 22. 16:12

2015. 1. 22. 16:12

쿼리중에 문법이 다르게 써야 하는... xml 을 사용하는 쿼리라 XQuery...

할때마다 찾는게 힘들어서 남깁니다.

xquery 전반적인 문법... 다

http://www.w3schools.com/xquery/default.asp

xquery가지고 수정, 삽입, 삭제등의 방법

http://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

저작자표시 비영리 변경금지

'DataBase' 카테고리의 다른 글

XQuery를 사용한 XML 데이터 업데이트 (0)	2013.09.17
프로시저 내 특정 단어 검색하기 (0)	2013.09.02
[MSSQL - GROUP BY HAVING 을 이용한 중복 데이타 체크] (0)	2013.08.29
실행 중인 쿼리의 처리 상태 확인 - sys.dm_exec_requests (0)	2013.08.01
MSSQL Lock 정보 확인 및 해제 (0)	2013.08.01

XQuery를 사용한 XML 데이터 업데이트

중년 플머 김씨 2013. 9. 17. 17:57

2013. 9. 17. 17:57

[참고 자료]
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

[출처]

2013-02-05 / 강성욱 / http://sqlmvp.kr / http://datawaffle.com

[출처] XQuery를 사용한 XML 데이터 업데이트|작성자 SungWook Kang

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)

INSERT HR_XML VALUES(

'<SALARIES>

</EMPLOYEE>

</EMPLOYEE>

</EMPLOYEE>

</MARKETING>

</SALARIES>

)

SELECT * FROM HR_XML

<Account / >라는 새로운 노드를 생성하려면 INSERT 구문을 이용하여 수행 할 수 있다.

UPDATE HR_XML

SET Salaries.modify('insert <Accounting /> into (/Salaries)[1]')

SELECT * FROM HR_XML

위의 쿼리에서 보면 Salaries [1] 노드에 대해 Singleton Designation 값이 지정된 것을 확인 할 수 있다.Singleton Designation 없이 쿼리를 실행하면 오류가 발생 한다.

UPDATE HR_XML

SET Salaries.modify('insert <Accounting /> into (/Salaries)')

[원하는위치 노드 추가]

노드를 추가하면 기존의 노드 이후에 삽입되는 것을 확인 할 수 있다. (위그림 참조). 원하는 위치에 노드를 삽입하는 방법을 알아 보자. 실습에서는 <Salaries> 아래 첫번째 노드에 삽입을 하여 보자. Firstinto 명령어를 이용한다.

UPDATE HR_XML

SET Salaries.modify('insert <Accounting /> as first into (/Salaries)[1]')

select * from HR_XML

첫번째 노드 <Account />에 Employee 노드(ID, tier)를 입력 해보자. 노드를 입력 할 때 속성을 포함해야 한다. SingletonDesignation [2] 지정으로 <Account>노드 바로 아래 <Employee>가 삽입 되는 것을 확인 할 수 있다.

UPDATE HR_XML

SET Salaries.modify('insert <Employee ID="4" tier="4" /> into (/Salaries/Accounting)[1]')

select * from HR_XML

[특정노드를 식별하여 삽입]

특정 Employee의 노드에 새 노드를 삽입하여 보자. @ID 인수를 사용하여 Employee를 식별 할 수 있다. 실습에서는 Employee ID = 2인 노드를 찾아서 새로운 노드를삽입하였다.

UPDATE HR_XML

SET Salaries.modify('insert <Projects /> into (/Salaries/Marketing/Employee[@ID=("2")])[1]')

select * from HR_XML

[노드삽입과 값 삽입]

노드 삽입과 컬력션 삽입을 할 수 있다. 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]')

select * from HR_XML

[다른노드 값을 참조하여 노드 삽입]

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]')

select * from HR_XML

[노드삭제]

노드 삭제는 노드 삽입보다 간단하다. 표현식은

Delete Expression 이다. 노드 마지막의 <Account />노드를 삭제 하여 보자.

UPDATE HR_XML

SET Salaries.modify('delete (/Salaries/Accounting)[2]')

select * from HR_XML

[값삭제]

값 삭제는 텍스트() 함수를 사용하여 삭제 할 수 있다.

UPDATE HR_XML

SET Salaries.modify('delete

(/Salaries/Marketing/Employee[@ID=("2")]/Projects/Project[@ID="1"]/Description/text())[1]')

select * from HR_XML

[값수정]

Exployee ID = 2의<Salary> 값을 수정하여 보자.

UPDATE HR_XML

SET Salaries.modify('replace value of

(/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")')

select * from HR_XML

[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

select * from HR_XML

[수정제한]

XQuery에서 Modify() 메소드는수정하려는 값을 SELECT와 함께 사용 할 수 없다.

SELECT Salaries.modify('replace value of

(/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1]

with ("60000")')

FROM HR_XML

기존의 값을 조회하여 수정하려면 다음과 같이 변수를 이용하여 사용하여야 한다.

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

쿼리문에서 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")')

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)')

select * from HR_XML

[계층값 변경]

@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"')

select * from HR_XML

XQuery를 사용하여 XMLDML 실습을 하였다. 많은 XML 편지기가있지만 SSMS를 이용하여 속성값을 사용하여 정확하게 수정할 때에 매우 유용하게 사용 할 수 있을 듯하다.

저작자표시 비영리 변경금지

'DataBase' 카테고리의 다른 글

XQuery 관련 튜토리얼 (0)	2015.01.22
프로시저 내 특정 단어 검색하기 (0)	2013.09.02
[MSSQL - GROUP BY HAVING 을 이용한 중복 데이타 체크] (0)	2013.08.29
실행 중인 쿼리의 처리 상태 확인 - sys.dm_exec_requests (0)	2013.08.01
MSSQL Lock 정보 확인 및 해제 (0)	2013.08.01

MSSQL-XQuery에 대해서...

중년 플머 김씨 2013. 3. 8. 09:41

2013. 3. 8. 09:41

XQuery 지원기능

XQuery는 XML에 대한 쿼리 언어입니다. XML 데이터를 조회하기 위한 XQuery 표현식 기반 xml 데이터형의 메서드를 살펴보고 XQuery 구문에 대해서 살펴봅니다. XQuery 구문에는 XPath 2.0 표현식이 포함되어 있으며, XQuery를 사용하여 XML 데이터원본에 복잡한 쿼리를 실행할 수 있습니다. SQL Server 에 제공하는 xml 데이터형에서는 XQuery 표현식을 사용하여 xml 데이터를 조회하고 변경하기 위해서 사용할 수 있는 메서드를 제공합니다.
SQL Server 2005의 XQuery 지원기능은 W3C XQuery 1.0 언어 스펙을 기초로 합니다.
( http://www.w3.org/XML/Query 참조)

XQuery 구문

XQuery는 크게 두가지 부분으로 구성됩니다. 네임스페이스를 선언하고, 스키마를 추가하기 위한 머리글 부분(생략가능)과 xml 데이터를 조회하기 위해서 사용하는 실제 XQuery 표현식이 포함된 본문 부분으로 나눌 수 있습니다. XQuery 표현식은 조회하고자 하는 XML 노드에 대한 간략한 경로 정보일 수도 있고, XML 결과를 생성하기 위한 복잡한 표현식일 수도 있습니다.

XQuery 경로는 XPath 언어를 기반으로 하며, XML 문서상에서 조회하고자 하는 노드의 위치를 나타냅니다. 경로는 절대경로(루트 엘리먼트로부터 XML 트리의 특정 노드의 위치를 표현)로 지정될 수도 있고, 상대경로(이미 알려진 노드로부터 조회하고자 하는 노드의 위치를 상대적으로 표현)로 지정될 수도 있습니다. 다음의 표는 간단한 XQuery 경로 예제를 나타냅니다.

예제 경로	설명
/InvoiceList/Invoice	<INVOICELIST>루트 엘리먼트에 포함되어 있는 모든 <INVOICE>엘리먼트</INVOICE></INVOICELIST>
(/InvoiceList/Invoice) [2]	<INVOICELIST>루트 엘리먼트에 포함되어 있는 두번째 <INVOICE>엘리먼트</INVOICE></INVOICELIST>
(InvoiceList/Invoice/@InvoiceNo) [1]	<INVOICELIST>루트 엘리먼트에 포함되어 있는 첫번째 <INVOICE>엘리먼트의 InvoiceNo 속성</INVOICE></INVOICELIST>
(InvoiceList/Invoice/Customer/text( ))[1]	<INVOICELIST>루트 엘리먼트에 포함되어 있는 <INVOICE>엘리먼트의 하위 첫번째 <CUSTOMER>엘리먼트의 텍스트</CUSTOMER></INVOICE></INVOICELIST>
/InvoiceList/Invoice[@InvoiceNo=1000	<INVOICELIST>루트 엘리먼트에 포함되어 있는 모든 <INVOICE>엘리먼트 중에서 InvoiceNo 속성이 1000인 <INVOICE>엘리먼트</INVOICE></INVOICE></INVOICELIST>

FLOWR 문장

XQuery 언어 스펙에 포함되어 있는 for, let, order by, where, return 문장을 통상적으로 FLOWR “( flower”라고 읽음) 라고 부릅니다. SQL Server 2005에서는 for, where, return 문장을 지원하며, 각각의 사용용도는 다음과 같습니다.

문장	설명
For	XML 문서의 동일 수준의 노드를 반복하며 처리하기 위해서 사용합니다.
Where	노드를 반복처리할 때 필터링 조건을 지정하기 위해서 사용합니다. XQuery 언어에는 Where 문장과 함께 사용할 수 있는 count( )와 같은 함수가 포함되어 있습니다.
Return	반복구조내에서 반환할 XML을 지정하기 위해서 사용합니다.

다음의 예제는 각 엘리먼트별로 자식 엘리먼트에 포함된 하나 이상의 엘리먼트의 목록을 반환합니다.

for $i in /InvoiceList/Invoice
where count($i/Items/Item) > 1
return $i

XQuery 표현식 사용예제

XQuery 표현식 중 가장 일반적으로 사용되는 유형을 예제와 함께 소개합니다.

원본XML
   <InvoiceList>
      <Invoice InvoiceNo=”1000”>
         <Customer>Kim Abercrombie</Customer>
         <Items>
            <Item Product=”1”Price=”1.99”Quantity=”2”/>
            <Item Product=”3”Price=”2.49”Quantity=”1”/>
         </Items>
      </Invoice>
      <Invoice InvoiceNo=”1001”>
         <Customer>Sean Chai</Customer>
         <Items>
            <Item Product=”1”Price=”1.99”Quantity=”2”/>
      </Items>
   </Invoice>
</InvoiceList>

단순한 XQuery 표현식 사용

구문 : /InvoiceList/Invoice

결과값
   <Invoice InvoiceNo=”1000”>
      <Customer>Kim Abercrombie</Customer>
      <Items>
         <Item Product=”1”Price=”1.99”Quantity=”2”/>
         <Item Product=”3”Price=”2.49”Quantity=”1”/>
      </Items>
   </Invoice>
   <Invoice InvoiceNo=”1001”>
      <Customer>Sean Chai</Customer>
      <Items>
         <Item Product=”1”Price=”1.99”Quantity=”2”/>
   </Items>
   </Invoice>

XQuery 조건 사용

구문 : /InvoiceList/Invoice[@InvoiceNo=1000]

결과값
   <Invoice InvoiceNo=”1000”>
      <Customer>Kim Abercrombie</Customer>
      <Items>
         <Item Product=”1”Price=”1.99”Quantity=”2”/>
         <Item Product=”3”Price=”2.49”Quantity=”1”/>
      </Items>
   </Invoice>

For 문장과 Return 문장 사용

구문
for $i in /InvoiceList/Invoice/Items/
Item[../../@InvoiceNo=1000]
return $i

결과값
   <Item Product=”1”Price=”1.99”Quantity=”2”/>
   <Item Product=”3”Price=”2.49”Quantity=”1”/>

For 문장과 Return 문장을 사용하여 XML 생성

구문
<OrderedItems>
   {
      for $i in /InvoiceList/Invoice/Items/Item
      return $i
   }
</OrderedItems>

결과값
<OrderedItems>
      <Item Product=”1”Price=”1.99”Quantity=”2”/>
      <Item Product=”3”Price=”2.49”Quantity=”1”/>
      <Item Product=”1”Price=”1.99”Quantity=”2”/>
</OrderedItems>

For 문장과 Return 문장을 사용하여 속성과 값을 반환

구문
<OrderedItems>
{
for $i in /InvoiceList/Invoice/Items/Item
return <Product>
{$i/@Quantity}
{string($i/@Product)}
</Product>
}
</OrderedItems>

결과값
   <OrderedItems>
      <Product Quantity=”2”>1</Product>
      <Product Quantity=”1”>3</Product>
      <Product Quantity=”2”>1</Product>
   </OrderedItems>

For, Where, Return 문장 사용

구문
<MultiItemInvoices>
{
for $i in /InvoiceList/Invoice
where count($i/Items/Item) > 1
return $i
}
</MultiItemInvoices>

결과값
   <MultiItemInvoices>
      <Invoice InvoiceNo=”1000”>
         <Customer>Kim Abercrombie</Customer>
         <Items>
            <Item Product=”1”Price=”1.99”Quantity=”2”/>
            <Item Product=”3”Price=”2.49”Quantity=”1”/>
         </Items>
   </Invoice>
</MultiItemInvoices>

머리글에 네임스페이스 지정

구문
   declare namespace awi =
   “http://schemas.adventure-works.com/Invoices”;
   /awi:InvoiceList/awi:Invoice[@InvoiceNo=1000]

결과값
   <awi:Invoice xmlns:awi=”http://schemas.adventure-works.com/Invoices”
                  InvoiceNo=”1000”>
         <awi:Customer>Kim Abercrombie</awi:Customer>
         <awi:Items>
            <awi:Item Product=”1”Price=”1.99”Quantity=”2”/>
            <awi:Item Product=”3”Price=”2.49”Quantity=”1”/>
         </awi:Items>
   </awi:Invoice>

기본 네임스페이스 사용

구문
   declare default namespace =
   “http://schemas.adventure-works.com/Invoices”;
   /InvoiceList/Invoice[@InvoiceNo=1000]

결과값
   <Invoice xmlns=”http://schemas.adventure-works.com/Invoices”
            InvoiceNo=”1000”>
       <Customer>Kim Abercrombie</Customer>
           <Items>
             <Item Product=”1”Price=”1.99”Quantity=”2”/>
             <Item Product=”3”Price=”2.49”Quantity=”1”/>
          </Items>
   </Invoice>

XML 데이터형에서 제공하는 메서드를 사용하여 쿼리실행

SQL Server 2005 XML 데이터형은 XML 데이터를 쿼리하고 수정하기 위해서 사용할 수 있는 네 가지 메서드를 제공합니다. 각 메서드는 대부분의 개발자에게 익숙한 구문인 데이터형.메서드_명칭으로 호출할 수 있습니다. 각 메서드의 기능에 대해서 이해하면, XML 데이터를 데이터베이스에서 처리하는 어플리케이션을 개발하는데 도움이 될 것입니다.

Query 메서드

Query 메서드는 XML 데이터형에 저장된 데이터에서 XML을 추출하기 위해서 사용합니다. Query 메서드의 매개변수로 전달되는 XQuery 표현식에 지정된 결과값이 조회됩니다.

SELECT xmlCol.query‘( declare default namespace =
“http://schemas.adventure-works.com/InvoiceList”;
<InvoiceNumbers>
{
for $i in /InvoiceList/Invoice
return <InvoiceNo>
{number($i/@InvoiceNo)}
</InvoiceNo>
}
</InvoiceNumbers>’

Value 메서드

Value 메서드는 XML 문서로부터 단일 값을 반환하기 위해서 사용합니다. value 메서드를 사용하기 위해서는 XQuery 표현식을 XML 데이터에 포함된 단일 노드를 식별할 수 있는 형태로 지정해야 하며, 반환되는 값이 T-SQL 데이터형이 되도록 지정해야 합니다.

SELECT xmlCol.value‘( declare default namespace =
“http://schemas.adventure-works.com/InvoiceList”;
/InvoiceList/Invoice/@InvoiceNo)[1]’,‘ int’

Exist 메서드

Exist 메서드는 XML 문서에 지정된 노드가 존재하는지 여부를 판단하기 위해서 사용합니다.
Exist 메서드가 1을 반환하면, 지정된 노드가 XML 문서내에서을 반환하면, 지정된 노드가 존재하지 않는다는 것을 의미합니다.

SELECT xmlCol.exist‘( declare default namespace =
“http://schemas.adventure-works.com/InvoiceList”;
/InvoiceList/Invoice[@InvoiceNo=1000]’

관계형 테이블의 컬럼과 변수의 바인딩

SQL Server 2005에서는 XQuery 언어를 사용하여 xml 데이터형 컬럼을 조회하기 위한 메서드가 포함된 SELECT 문장에서, 관계형 데이터 컬럼을 참조할 수 있도록 지원합니다. XML 데이터형 컬럼으로부터 XML 데이터를 조회하기 위해, XML 데이터형 메서드가 포함된 SELECT 문장에서, sql:column 함수를 사용하여, XML 데이터안에 비-xml 데이터 컬럼값을 포함시킬 수 있습니다. 또한, sql:variable 확장을 사용하여, 저장 프로시저내에서 변수를 참조할 수 있습니다. XML 데이터내부에 비-xml 컬럼 값을 포함시키기 위해, sql:column 함수를 사용하는 예제는 다음과 같습니다.

SELECT StoreName, Invoices.query‘( declare default namespace=
“http://schemas.adventure-works.com/Invoices”;
<Invoices>
<Store>{sql:column“( StoreName”)}</Store>
{
for $i in /InvoiceList/Invoice
return $i
}
</Invoices>’) InvoicesWithStoreName
FROM Stores

Modify 메서드를 사용하여 XML 데이터 변경

XML 데이터형 컬럼에 저장된 XML 데이터를 변경하기 위해서 Modify 메서드를 사용합니다.
Modify 메서드는 XQuery 언어 스펙에 대해 insert, replace, delete 확장기능을 지원합니다. 세 가지 확장기능은 XML DML로서 참조할 수 있습니다.

INSERT 문장을 사용하여 XML 데이터에 노드를 추가할 수 있습니다.
REPLACE 문장을 사용하여 XML 데이터를 변경할 수 있습니다.
DELETE 문장을 사용하여 XML 데이터에서 특정 노드를 삭제할 수 있습니다.

INSERT 문장

Modify 메서드와 함께 INSERT 문장을 사용하여, XML 데이터형 컬럼이나 변수에 저장된 XML 데이터에 노드를 추가할 수 있습니다. INSERT 문장에 대한 구문은 다음과 같습니다.

insert Expression1 (
{as first | as last} into | after | before
Expression2 )

INSERT 키워드에 지정할 수 있는 매개변수는 다음과 같습니다.

매개변수	설명
Expression1	추가될 노드를 지정하기 위한 표현식으로, XML 문자열 형식으로 지정해야 합니다. (예를 들어,<Item Product=”5”Quantity=”1”/>) 또한, 텍스트 노드에 추가할 엘리먼트 표현식을 지정할 수도 있습니다. (예를 들어, element SalesPerson {“ Bill”}) 마지막으로, 속성에 추가할 속성 표현식을 지정할 수 있습니다. (예를 들어, attribute discount {“ 1.50”})
as first	계층구조의 첫번째 노드에 새로운 XML을 추가하기 위해서 사용합니다.
as last	계층구조의 마지막 노드에 새로운 XML을 추가하기 위해서 사용합니다.
Into	Expression2 위치에 Expression1을 추가하기 위해서 사용합니다.
After	Expression2 뒤에 Expression1을 추가하기 위해서 사용합니다.
Before	Expression2 앞에 Expression1을 추가하기 위해서 사용합니다.
Expression2	XML 문서에 포함된 기존 노드를 지정하기 위한 XQuery 표현식.

다음 예제는 Modify 메서드와 함께 INSERT Xquery 문장을 사용하는 방법을 나타냅니다.

SET @xmlDoc.modify
‘( declare default namespace = “ http://schemas.adventureworks.
com/InvoiceList”;
   insert element salesperson “{ Bill”}
   as first into (/InvoiceList/Invoice)[1]’

replace 문장

XML 데이터를 변경하기 위해서 modify 메서드와 함께 REPLACE 문장을 사용합니다.
REPLACE 문장에 대한 구문은 다음과 같습니다.

replace value of
Expression1
with
Expression2

REPLACE 문장에서 사용할 수 있는 매개변수는 다음과 같습니다.

매개변수	설명
Expression1	값을 변경할 노드를 지정하기 위한 XQuery 표현식
Expression2	대체할 노드에 새로 지정할 값

다음의 예제는 Modify 메서드와 함께 REPLACE 문장을 사용하는 방법을 나타냅니다.

SET xmlCol.modify
 ‘( declare default namespace =”http://schemas.adventure-works.com/InvoiceList”;
       replace value of (/InvoiceList/Invoice/SalesPerson/text( ))[1]
       with“ Ted”’)

DELETE 문장

XML 데이터에서 지정된 노드를 삭제하기 위해서 Modify메서드와 함께 DELETE 문장을 사용합니다. DELETE 문장에 대한 구문은 다음과 같습니다.

delete Expression

Expression 매개변수는 삭제할 노드를 지정하기 위한 XQuery 표현식입니다. 다음 예제는 modify 메서드와 함께 DELETE 문장을 사용하는 방법을 나타냅니다.

SET xmlCol.modify
‘( declare default namespace =”http://schemas.adventure-works.com/InvoiceList”;
    delete (/InvoiceList/Invoice/SalesPerson)[1]’)

Nodes 메서드를 사용하여 XML 데이터 부분추출

Xml 데이터형에서는 nodes 메서드를 통해, XML 데이터를 관계형 테이블 형식으로 생성할 수 있는 기능을 제공합니다. Nodes 메서드는 XQuery 표현식으로 지정된 각 노드를 행집합 형식으로 반환합니다.

nodes 메서드의 구문은 다음과 같습니다.

xmlvalue.nodes (XQuery) [AS] Table(Column)

Nodes 메서드에서 사용할 수 있는 매개변수는 다음과 같습니다.

매개변수	설명
Xmlvalue	XML 데이터형 변수 또는 컬럼
XQuery	반환하고자 하는 노드를 지정하기 위한 XQuery 표현식
Table(Column)	결과값으로 반환할 테이블명과 컬럼명. 결과값 테이블은 순차적으로 수행되는 쿼리에서 데이터를 추출하기 위한 원본으로 사용될 수 있습니다.

Xml 데이터형 변수나 컬럼에서 nodes 메서드를 사용하여 관계형 테이블 형식의 데이터를 조회할 수 있습니다.

XML 데이터형 변수에서 관계형 테이블 형식의 데이터를 추출하기 위해서, nodes 메서드에서
반환하는 결과행집합에 대해서, query, value, exist와 같은 메서드를 사용합니다.
Xml 데이터형 컬럼에서 관계형 테이블 형식의 데이터를 반환하기 위해서, nodes 메서드와 함께
APPLY 연산자를 사용합니다.

XML 변수에 대해 nodes 메서드 사용법

Xml 데이터형 변수에서 관계형 테이블 형식의 데이터를 추출하기 위해서, nodes 메서드에서 반환하는 행집합에 query, value, exist 메서드를 사용할 수 있습니다. 다음 예제는 xml 변수에서 관계형 테이블 형식의 주문 데이터를 추출하는 방법을 나타냅니다.

DECLARE @xmlOrder xml
SET @xmlOrder =‘ <?xml version=”1.0”?>
   <Order OrderID=”1000”OrderDate=”2005-06-04”>
      <LineItem ProductID=”1”Price=”2.99”Quantity=”3”/>
      <LineItem ProductID=”2”Price=”3.99”Quantity=”1”/>
   </Order>’

SELECT nCol.value‘( @ProductID’,‘ integer’) ProductID,
   nCol.value‘( @Quantity’,‘ integer’) Quantity
FROM @xmlOrder.nodes‘( /Order/LineItem’) AS nTable(nCol)

위의 코드를 실행하면 다음과 같은 결과가 반환됩니다.

ProductID	Quantity
1	3
2	1

XML 컬럼에 대해 nodes 메서드 사용하는 방법

Xml 컬럼에서 관계형 테이블 형식의 데이터를 반환하게 하기 위해서, nodes 메서드와 함께 APPLY 연산자를 사용합니다. 다음 예제는 nodes 메서드를 사용하여 XML 컬럼으로부터 주문 데이터를 추출하는 방법을 나타냅니다.

SELECT nCol.value‘( ../@OrderID[1]’,‘ int’) OrderID,
   nCol.value‘( ../@OrderDate[1]’,‘ datetime’) OrderDate,
   nCol.value‘( @ProductID[1]’,‘ int’) ProductID,
   nCol.value‘( @Price[1]’,‘ money’) Price,
   nCol.value‘( @Quantity[1]’,‘ int’) Quantity
FROM Orders_X
CROSS APPLY OrderDoc.nodes‘( /Order/LineItem’) AS nTable(nCol)

위의 코드는 다음과 같은 결과값을 반환합니다.

OrderID	OrderDate	ProductID	Price	Quantity
1000	2005-06-04 00:00:00.000	1	2.99	1
1000	2005-06-04 00:00:00.000	2	3.99	2
1000	2005-06-04 00:00:00.000	2	3.99	1
1002	2005-06-04 00:00:00.000	1	3.99	1

출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=13815&boardConfigUid=9&categoryUid=216&boardIdx=72&boardStep=1

저작자표시 비영리 변경금지

'DataBase' 카테고리의 다른 글

SELECT * INTO와 INSERT INTO SELECT..의 차이점 (0)	2013.04.19
특정 컬럼명을 사용하는 모든 테이블 검색하는 쿼리 (0)	2013.04.15
XML 문서 가져오기 및 XQuery 쿼리 예제 (0)	2013.02.26
NoSQL은 생각보다 쓸만하지 않다. (0)	2012.09.26
MSSQL 2005 메모리 관리 (0)	2012.08.09

XML 문서 가져오기 및 XQuery 쿼리 예제

중년 플머 김씨 2013. 2. 26. 16:44

2013. 2. 26. 16:44

출처 : http://laigo.kr/723

SQL Server 2012 에서 XML 문서를 테이블에 Bulk Insert 하는 방법과 XQuery 를 사용하여 XML 데이터를 검색하는 몇 가지 예제를 아래와 같이 정리하였습니다.

1. XML 원본 문서를 테이블에 Bulk Insert 하는 방법

CREATE DATABASE XMLDB
GO

USE XMLDB
GO

CREATE TABLE TBL_XML (
ID INT IDENTITY PRIMARY KEY,
XMLDATA XML
)
GO

INSERT INTO TBL_XML(XMLDATA)
SELECT * FROM OPENROWSET (
BULK 'D:\SQL\BOOKS.XML', SINGLE_BLOB) AS x;

2. XQuery 를 사용하여 XML 데이터 검색 예제

CREATE TABLE TBL_XQUERY (
ID INT IDENTITY PRIMARY KEY,
XMLDATA XML
)
GO

INSERT INTO TBL_XQUERY VALUES ('
<CATALOG>
<book id="bk101">
   <author>Gambardella, Matthew</author>
   <title>XML Developer''s Guide</title>
   <genre>Computer</genre>
   <price>44.95</price>
   <publish_date>2000-10-01</publish_date>
   <description>An in-depth look at creating applications with XML.</description>
</book>
</CATALOG>
')

INSERT INTO TBL_XQUERY VALUES ('
<CATALOG>
<book id="bk102">
    <author>Ralls, Kim</author>
    <title>Midnight Rain</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2000-12-16</publish_date>
    <description>A former architect battles corporate zombies, n evil sorceress, and her own childhood to become queen of the world.</description>
</book>
</CATALOG>
')

INSERT INTO TBL_XQUERY VALUES ('
<CATALOG>
    <book id="bk103">
    <author>Corets, Eva</author>
    <title>Maeve Ascendant</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2000-11-17</publish_date>
    <description>After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society.</description>
    </book>
</CATALOG>
')

SELECT XMLDATA.query('/CATALOG/book')
FROM TBL_XQUERY
WHERE ID = 2
/*
<book id="bk102">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-12-16</publish_date>
<description>A former architect battles corporate zombies, n evil sorceress, and her own childhood to become queen of the world.</description>
</book>
*/

SELECT XMLDATA.value ( '(/CATALOG/book/@id)[1]', 'char(5)' ) AS 'Book id'
FROM TBL_XQUERY
/*
bk101
bk102
bk103
*/

SELECT XMLDATA
FROM TBL_XQUERY
WHERE XMLDATA.exist('/CATALOG/book[@id="bk103"]') = 1
/*
<CATALOG>
<book id="bk103">
    <author>Corets, Eva</author>
    <title>Maeve Ascendant</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2000-11-17</publish_date>
    <description>After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society.</description>
</book>
</CATALOG>
*/

SELECT
nCol.value('@id', 'char(5)') AS 'Book id',
nCol.value('(author)[1]', 'varchar(10)') AS author,
nCol.value('(title)[1]', 'varchar(50)') AS title,
nCol.value('(genre)[1]', 'varchar(10)') AS genre
FROM TBL_XQUERY
CROSS APPLY XMLDATA.nodes('/CATALOG/book') AS nTbl(nCol)
ORDER BY ID
/*
bk101 Gambardell XML Developer's Guide Computer
bk102 Ralls, Kim Midnight Rain Fantasy
bk103 Corets, Ev Maeve Ascendant Fantasy
*/

[참고자료]

Examples of Bulk Import and Export of XML Documents (SQL Server)
http://msdn.microsoft.com/en-us/library/ms191184.aspx

XQuery 언어 참조(SQL Server)
http://msdn.microsoft.com/ko-kr/library/ms189075.aspx

저작자표시 비영리 변경금지

'DataBase' 카테고리의 다른 글

특정 컬럼명을 사용하는 모든 테이블 검색하는 쿼리 (0)	2013.04.15
MSSQL-XQuery에 대해서... (0)	2013.03.08
NoSQL은 생각보다 쓸만하지 않다. (0)	2012.09.26
MSSQL 2005 메모리 관리 (0)	2012.08.09
MS-SQL 특정 테이블 데이터를 백업/복구 (0)	2012.08.07

PREV 이전 1 NEXT 다음

기억창고

XQuery

XQuery 관련 튜토리얼

'DataBase' 카테고리의 다른 글

XQuery를 사용한 XML 데이터 업데이트

'DataBase' 카테고리의 다른 글

MSSQL-XQuery에 대해서...

XQuery 지원기능

XQuery 구문

FLOWR 문장

XQuery 표현식 사용예제

단순한 XQuery 표현식 사용

XQuery 조건 사용

For 문장과 Return 문장 사용

For 문장과 Return 문장을 사용하여 XML 생성

For 문장과 Return 문장을 사용하여 속성과 값을 반환

For, Where, Return 문장 사용

머리글에 네임스페이스 지정

기본 네임스페이스 사용

XML 데이터형에서 제공하는 메서드를 사용하여 쿼리실행

Query 메서드

Value 메서드

Exist 메서드

관계형 테이블의 컬럼과 변수의 바인딩

Modify 메서드를 사용하여 XML 데이터 변경

INSERT 문장

replace 문장

DELETE 문장

Nodes 메서드를 사용하여 XML 데이터 부분추출

XML 변수에 대해 nodes 메서드 사용법

XML 컬럼에 대해 nodes 메서드 사용하는 방법

'DataBase' 카테고리의 다른 글

XML 문서 가져오기 및 XQuery 쿼리 예제

'DataBase' 카테고리의 다른 글

+ Recent posts

티스토리툴바