Programming/Microsoft SQL Server 2010.08.10 12:53
For Xml은 기존 Table형태의 결과를 XML형태로 바꿔서 조회하는 기능을 제공합니다. 반면 OpenXml은 For Xml과 반대로 XML형태의 Data를 Table형태로 바꿔서 조회하는 기능을 가지고 있습니다.

우선 AdventureWorks2008예제 Database의 HumanResources.Department Table을 XML형태로 조회하여 간단한 XML Data를 생성하도록 하겠습니다.

Select Top(3) 1 As Tag,
 Null As Parent,
 DepartmentID As [Department!1!ID],
 Null As [Names!2!Nm],
 Null As [Group!3!Name!Element]
From HumanResources.Department
Union
Select Top(3) 2 As Tag,
 1 As Parent,
 DepartmentID,
 Name,
 Null
From HumanResources.Department
Union
Select Top(3) 3 As Tag,
 2 As Parent,
 DepartmentID,
 Name,
 GroupName
From HumanResources.Department
Order By [Department!1!ID], [Names!2!Nm], [Group!3!Name!Element]
For XML Explicit;


위에서 만들어진 XML Data를 XML 변수에 저장합니다.

Declare @MyXml Xml;
Set @MyXml = '
<TopEle>
<Department ID="1">
  <Names Nm="Engineering">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="2">
  <Names Nm="Tool Design">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="3">
  <Names Nm="Sales">
    <Group>
      <Name>Sales and Marketing</Name>
    </Group>
  </Names>
</Department>
</TopEle>';

XML형 Data변수에 XML내용을 저장합니다.(최상위 요소인 <TopEle>는 원래 XML에서 최상위 요소가 존재해야 한다는 제약으로 인해 새로 추가시킨 최상위 요소입니다.)

이제 다음 순서로 XML 문서의 구문분석 기능을 갖고 있는  sp_xml_preparedocment System Procedure를 사용하여 해당 XML을 분석하도록 해야 합니다. sp_xml_preparedocument Procedure는 인수로 전달된 XML Data에 대한 handle값을 정수형태로 반환하므로 이후 부터 XML문서를 조작할때는 이 Handle값을 이용하게 됩니다.

Declare @MyXml Xml;
Set @MyXml = '
<TopEle>
<Department ID="1">
  <Names Nm="Engineering">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="2">
  <Names Nm="Tool Design">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="3">
  <Names Nm="Sales">
    <Group>
      <Name>Sales and Marketing</Name>
    </Group>
  </Names>
</Department>
</TopEle>';

Declare @iHandle Int;
Exec sp_xml_preparedocument @iHandle Output, @MyXml;


sp_xml_preparedocument Procedure를 통해 @iHandle에 해당 handle값을 저장합니다.

여기까지 OpenXml을 사용할 준비가 완료된 것입니다. OpenXml사용시에는 다음과 같이 위에서 얻어진 Handle값을 인수로 주고 출력할 기준이 되는 요소를 지정해서 사용면 됩니다.

Declare @MyXml Xml;
Set @MyXml = '
<TopEle>
<Department ID="1">
  <Names Nm="Engineering">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="2">
  <Names Nm="Tool Design">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="3">
  <Names Nm="Sales">
    <Group>
      <Name>Sales and Marketing</Name>
    </Group>
  </Names>
</Department>
</TopEle>';

Declare @iHandle Int;
Exec sp_xml_preparedocument @iHandle Output, @MyXml;

Select *
From OpenXml(@iHandle, '/TopEle/Department')
Order By id;


OpenXml을 통하여 sp_xml_preparedocument에서 구분분석된 XML Data를 TopEle의 Department요소를 중심으로 출력하도록 합니다.


위 Query결과를 보면 XML Data에 대한 내용이 아니라 sp_xml_preparedocument를 통해 구문분석된 결과를 보여고 있다는 것을 알 수 있습니다.

이 결과 Table에서 가장 첫번째 id는 XML을 분석한 요소, 속성, 값등 XML을 이루고 있는 구조 하나하나를 모두 나열하고 거기에 번호를 붙여 id로 표시한 겁니다.

두번째 parentid는 해당 id의 요소가 어떤 요소에 속해 있는지를 나타내는 것입니다. 우선 첫번째 행과 두번째 행만을 살펴보자면 id가 2번인 요소는 Department요소로서 부모 요소는 TopEle가 되며 이 최상위 요소는 id가 0이므로 parentid가 0으로 표시되고 있습니다. 또한 id가 3인 요소는 ID요소로 이 ID요소는 id가 2번인 Department에 속해 있는 속성이기 때문에 parentid값으로 2가 나타나고 있습니다.

세번째 nodetype는 해당 요소가 어떤 type인지를 나타내고 있는 것으로 이 값이 1이면 XML의 요소, 2이면 속성, 3이면 속성의 값을 의미합니다.

네번째 localname은 요소의 이름이고 여덟번째 prev는 요소가 단순한 하위요소가 아니라 새로 시작되는 Group요소일 경우 이전에 먼저 시작된 상위 Group의 id를 의미합니다.

아홉번째 text는 nodetype이 3(속성)일 경우 해당 속성의 값을 표시하는 부분입니다.

위 결과를 가지고 이제 OpenXml에 With option을 추가하여 원래 의도한 결과를 다시 조회해 보겠습니다.

Declare @MyXml Xml;
Set @MyXml = '
<TopEle>
<Department ID="1">
  <Names Nm="Engineering">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="2">
  <Names Nm="Tool Design">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="3">
  <Names Nm="Sales">
    <Group>
      <Name>Sales and Marketing</Name>
    </Group>
  </Names>
</Department>
</TopEle>';

Declare @iHandle Int;
Exec sp_xml_preparedocument @iHandle Output, @MyXml;

Select *
From OpenXml(@iHandle, '/TopEle/Department', 1)
With (ID Int);

Select *
From OpenXml(@iHandle, '/TopEle/Department/Names', 1)
With (Nm Char(15));


두번째와 세번째 Select문을 주목해 주십시오. 첫번째 OpenXml에는 /TopEle/Department 가 지정되어 Department요소를 중심으로 하겠다고 하는 것이며 뒤에 1은 속성을 조회한다는 의미합니다. With을 통해 ID Int라고 지정했으므로 결국 Department요소에서 ID속성의 값을 Int형으로 가져오라는 뜻이 됩니다.

두번째 Select문도 마찬가지 인데 /TopEle/Department/Names 로 지정되어 해당 요소의 Nm속성을 Char형으로 가져오게 합니다.


물로 위 두개의 Select문을 합쳐 다음과 같이 하나로 표시할 수도 있습니다.

Declare @MyXml Xml;
Set @MyXml = '
<TopEle>
<Department ID="1">
  <Names Nm="Engineering">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="2">
  <Names Nm="Tool Design">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="3">
  <Names Nm="Sales">
    <Group>
      <Name>Sales and Marketing</Name>
    </Group>
  </Names>
</Department>
</TopEle>';

Declare @iHandle Int;
Exec sp_xml_preparedocument @iHandle Output, @MyXml;

Select *
From OpenXml(@iHandle, '/TopEle/Department/Names', 1)
With (ID Int '../@ID', Nm Char(15));


With option을 보시면 ID int뒤에 '../@ID'라는 내용이 추가된걸 알 수 있습니다. 이것이 의미하는 것은 '상위요소(../)에서 ID속성(@ID)의 값을 가져온다.'는 뜻을 담고있습니다.

이렇게 하는 이유는 OpenXml에 요소지정시 /TopEle/Department/Names 라고 하였으므로 결국Names 요소 이하의 내용만 가져오게 되는데 이렇게 되면 Department요소의 ID속성값은 가져오지 못하는 문제가 생기게 되므로 '../@ID'를 통해 상위 요소의 속성값을 가져올 수 있도록 지정해야 하는 것입니다.

그리고 OpenXml로 조회시 가져올 항목이 늘어나면 With에 Column지정할때 콤마(,)를 사용하여 각 Column을 구분해 주시면 됩니다.


이번에는 Department ID속성의 내용과 더불어 Name요소의 Data값을 가져와 보도록 하겠습니다.

Declare @MyXml Xml;
Set @MyXml = '
<TopEle>
<Department ID="1">
  <Names Nm="Engineering">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="2">
  <Names Nm="Tool Design">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="3">
  <Names Nm="Sales">
    <Group>
      <Name>Sales and Marketing</Name>
    </Group>
  </Names>
</Department>
</TopEle>';

Declare @iHandle Int;
Exec sp_xml_preparedocument @iHandle Output, @MyXml;

Select *
From OpenXml(@iHandle, '/TopEle/Department/Names/Group', 2)
With (ID Int '../../@ID', Name Char(50));


Name의 Data값을 가져오기 위해 요소를 /TopEle/Department/Names/Group까지 지정하였습니다. 또한 속성의 값이 아닌 요소의 값을 가져오게 되므로 1에서 2로 바꿔 요소의 값을 가져오도록 하였습니다.

이때 ID는 기존에 /TopEle/Department/Names에서 /TopEle/Department/Names/Group이라고 한단계 더 내려간 요소를 지정하였으므로 ID int뒤에도 ../ 의 경로지정을 하나 더 붙여 상위 2개 요소위(../../)에서 ID속성의 값을 가져와야 한다고 지정하였습니다.

여기까지 보면 'OpenXml에서 2로 하여 요소의 값을 가져오도록 지정했는데 ID는 속성값이므로 문제가 되지 않을까?' 라고 의문을 가질 수 있지만 크게 문제되지는 않습니다. ID앞에 @문자를 붙여 '가져올 내용은 속성의 값이다.'라고 명시하였기 때문입니다.


참고 :
요소의 값과 속성을 모두 포함시키려면 다음처럼 3(요소1 + 속성2)을 지정해야 합니다.

From OpenXml(@iHandle, '/TopEle/Department/Names/Group', 3)

위에서 언급된 내용을 토대로 해당 XML의 모든 내용을 가져와 보도록 하보겠습니다.

Declare @MyXml Xml;
Set @MyXml = '
<TopEle>
<Department ID="1">
  <Names Nm="Engineering">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="2">
  <Names Nm="Tool Design">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="3">
  <Names Nm="Sales">
    <Group>
      <Name>Sales and Marketing</Name>
    </Group>
  </Names>
</Department>
</TopEle>';

Declare @iHandle Int;
Exec sp_xml_preparedocument @iHandle Output, @MyXml;

Select *
From OpenXml(@iHandle, '/TopEle/Department/Names/Group', 2)
With (ID Int '../../@ID', Nm Char(15) '../@Nm', Name Char(50));

Select Top(3) DepartmentID, Name, GroupName
From HumanResources.Department;



위 결과를 실제 Table의 내용과 비교해 보시기 바랍니다.

OpenXml을 사용할때 Select문을 보시면 일반 Table 조회와 별 차이가 없습니다. 따라서 일반 Table처럼 where문으로 특정한 조건의 내용만 가져올 수 있고 Max나 Sum처럼 수식을 정할 수도 있습니다.

Declare @MyXml Xml;
Set @MyXml = '
<TopEle>
<Department ID="1">
  <Names Nm="Engineering">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="2">
  <Names Nm="Tool Design">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="3">
  <Names Nm="Sales">
    <Group>
      <Name>Sales and Marketing</Name>
    </Group>
  </Names>
</Department>
</TopEle>';

Declare @iHandle Int;
Exec sp_xml_preparedocument @iHandle Output, @MyXml;

Select max(ID)
From OpenXml(@iHandle, '/TopEle/Department/Names/Group', 2)
With (ID Int '../../@ID', Nm Char(15) '../@Nm', Name Char(50));

Select *
From OpenXml(@iHandle, '/TopEle/Department/Names/Group', 2)
With (ID Int '../../@ID', Nm Char(15) '../@Nm', Name Char(50))
Where ID = 3;


첫번째 Selet에서는 ID가 가장 큰 값을 가져오도록 했으며 두번째 Select에서는 ID가 3인 내용만 조회되도록 하였습니다.


OpenXml을 사용하기 위해서는 XML을 분석할 sp_xml_preparedocument Procedure를 호출한다고 하였습니다.

이 호출된 sp_xml_preparedocument Procedure는 해당 XML Data를 Memory에 올려놓고 구문분석과 handle을 반환하는 역활을 수행합니다. 이때 Memory에 Load된 XML을 제거해 주지 않으면 계속해서 Memory를 잡아먹는 낭비를 가져올 수 있습니다.

따라서 해당 XML에 대해 적절한 처리를 수행하고 나면 다음 처럼 sp_removedocument Procedure를 사용하여 Memory에서 XML Data를 제거해야할 필요가 있습니다.

Declare @MyXml Xml;
Set @MyXml = '
<TopEle>
<Department ID="1">
  <Names Nm="Engineering">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="2">
  <Names Nm="Tool Design">
    <Group>
      <Name>Research and Development</Name>
    </Group>
  </Names>
</Department>
<Department ID="3">
  <Names Nm="Sales">
    <Group>
      <Name>Sales and Marketing</Name>
    </Group>
  </Names>
</Department>
</TopEle>';

Declare @iHandle Int;
Exec sp_xml_preparedocument @iHandle Output, @MyXml;

Select *
From OpenXml(@iHandle, '/TopEle/Department/Names/Group', 2)
With (ID Int '../../@ID', Nm Char(15) '../@Nm', Name Char(50));

Exec sp_xml_removedocument @iHandle;


sp_xml_removedocument Procedure에 XML의 handle을 넘겨 Memory에서 제거되도록 합니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 트랜잭션(Transaction)  (0) 2010.08.19
[SQL] 저장프로시저(Store Procedure)  (0) 2010.08.17
[SQL] OpenXml을 사용해 XML을 Table형태로 조회하기  (0) 2010.08.10
[SQL] XQuery로 XML Data에 접근하기  (0) 2010.08.09
[SQL] Identity  (0) 2010.07.20
[SQL] Data 집계하기  (0) 2010.07.19
, , , , , , , , , , ,
0 0