Programming/Microsoft SQL Server
XML 참고 : [Computer, IT] - XML을 써보자

SQL Server에서 특정 Data를 조회하기 위해 Query를 날리면 보통의 경우 Table형태로 그 결과를 출력하게 됩니다.

Select *
From HumanResources.Department;


하지만 필요한 경우 결과를 XML형태로도 조회할 수 있습니다. 구현 방법도 아주 간단한데 단지 Query 뒤에 for xml option을 추가하고 원하는 형식에 따라 raw, auto, explicit을 덧붙이기만 하면 됩니다.

1. Raw

Raw은 Xml형태로 결과를 출력할때 Table에 해당하는 각 Column을 요소의 속성으로 한뒤 Table의 실제 Data를 속성의 값으로 표현하도록 합니다.

Select DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw;


이때 각 행 요소의 이름은 row로 표시됩니다. 만일 이 이름이 마음에 들지 않는다면 괄호(())안에 원하는 이름을 지정해 줄 수 있습니다.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw('mytag');

요소 이름을 row가 아닌 mytag로 출력되도록 지정하였습니다.


Raw에 Elements요소를 추가하면 위에서처럼 단일 행으로 출력되는 형태가 아니라 각 행별(<row>)로 Column을 XML 요소로 하고 Table의 내용을 요소의 값으로 표현하도록 합니다.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw, Elements;


위 결과에서 Table의 Data를 요소의 값으로 표시하였으며 더불어 각 행마다 계층적으로 Query결과가 표현되었음을 확인할 수 있습니다. 이때 이 계층 구조의 요소에 최상위 요소를 추가하고자 하신다면 Root를 구문을 사용하십시오.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw, Elements, Root('topelement');

Root option으로 'ttopelement'라는 최상위 요소가 추가되도록 하였습니다.


참고:
이전까지의 내용도 그렇고 이후에 설명될 내용에서도 마찬가지지만 각 Option을 어떻게 사용할지는 개발자가 비교적 자유롭게 정할 수 있는 대목입니다. 예제에서도 Root는 마치 Elements option이 쓰여졌을때만 사용한다라는 오해의 소지를 갖게 될 수 있으나 실제로는 그렇지 않고 단독으로 쓸 수 있습니다.(물론 다른 Option도 특별한 경우를 제외하고는 모두 마찬가지 입니다.)

XML문서를 이루는 요소중의 하나로 DTD라는 것이 있습니다. 이 DTD를 통해 해당 XML문서의 구조를 알 수 있도록 하는데 Query에서 XmlData를 쓰면 그 결과로 XML이 표시될때 해당 XML이 어떻게 구성되어 있는지에 대한 DTD를 같이 나타내도록 할 수 있습니다.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw, XmlData;


XML출력결과의 상단에 Schema라는 이름으로 해당 XML구조를 나타내고 있습니다.

일반적인 Data형 Table의 경우에는 대부분 XML형태로 결과를 얻는데 큰문제가 없습니다. 그러나 Column의 Datatype형태가 Image나 Text와 같은경우에는 이 Data자체를 XML로 표현하기가 곤란할 수 있습니다. 따라서 해당 Column의 내용을 Binary로 변화시켜서 표현해야 하는데 이때 Binary Base64구문을 사용하시면 됩니다.

Select Top(2) ProductPhotoID, ThumbNailPhoto, ThumbnailPhotoFileName
From Production.ProductPhoto
For Xml Raw, Binary Base64;

AdventureWorks2008 예제 Database의 Production.ProductPhoto Table에는 ThumbNailPhoto라는 Image Datatype의 사진(그림)정보가 저장된 Column이 있습니다.

(실제 Binary Base64 출력결과를 보면 이보다 훨씬 길게 나타납니다.)

일반적인 방법으로 XML형태의 표현이 불가능한 경우는 또 있습니다. 바로 Table에 Null이 있는 경우 입니다.

Select Top(3) AddressID, AddressLine1, AddressLine2
From Person.Address
For Xml Raw;

Person.Address Table의 AddressID와 AddressLine1, AddressLine2를 3행까지 XML 형태로 출력하도록 합니다.


결과를 놓고 보니 뭔가 좀 이상하다는 생각할 할 수 있습니다. 분명 Query에서는 AddressLine2까지 표시하라고 했지만 AddressLine1만 나왔기 때문입니다. 이는 XML출력시에 Null은 결과에서 아예 제외하기 때문입니다. 이러한 현상을 막으려면 Xsinil Option을 써야합니다.

Select Top(3) AddressID, AddressLine1, AddressLine2
From Person.Address
For Xml Raw, Elements Xsinil;

Xsinil이 Elements와 함께 구현되었습니다.


결과에서는 Null에 해당하는 부분의 속성이 xsi:nil="true" 형태로 나타나 이 요소에 대한 Column의 내용이 null이라는 것을 알려주고 있습니다.

2. Auto

Raw사용시 이름을 지정해 주지 않으면 XML의 각 요소를 row항목으로 표시했었는데 Auto는 row가 아닌 조회되는 해당 Table명으로 XML의 요소를 이루도록 합니다.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Auto;


row대신 해당 Table명으로 각 요소를 이루고 있습니다.

어떻게 보면 Raw에서 Table영으로 이름을 지정해 준것과 결과가 동일하다고 볼 수 있겠습니다. 아무래도 의미없는 row대신 Table명으로 요소를 자동표시한다고 해여 이름이 auto인듯 합니다.^^;;

참고:
Table이름에 As구문을 사용하여 다른 이름을 부여했다면 해당 이름으로 요소명이 지정됩니다.

3. Explicit

Explicit는 Raw나 Auto와는 달리 출력되는 XML의 각 요소를 비교적 자유롭게 작성할 수 있도록 해줍니다. Result set을 원하는 XML형태로 조회하는 것이 가능하다는 얘기인데 기능이 우수한 만큼 사용하기도 다소 번거롭습니다.

일단 간단하게나마 짚어보도록 하겠습니다.

Select 1 As Tag, Null As Parent, DepartmentID As [Department!1!ID]
From HumanResources.Department
Order By [Department!1!ID]
For XML Explicit;

HumanResources.Department Table의 DepartmentID Column을 Explicit style로 출력합니다.

Select에서 1 As Tag는 출력할 Tag요소가 하나(1)인것만을 지정한다는 의미로 해석하시면 됩니다. 또한 출력하려는 요소의 부모요소는 현재 존재하지 않으므로 Null As Parent로 부모 요소를 지정합니다.(나중에 설명 드리겠지만 요소를 늘리려면 조회하는 구문을 하나 더 써서 Union으로 Join해야 합니다.)

[Department!1!ID]에서 Department는 Department요소를 추가시킨다는 의미 입니다. 뒤이은 !은 요소와 요소의 차수 그리고 속성을 구분해 주는 구분자 역활을 합니다.

차수는 현재 1단계 level의 요소만을 출력할 것이므로(Select 에서 1 As Tag로 하여 출력할 Tag요소가 하나라는 것을 이미 정의하였습니다.)1이라고 쓰고 다시 구분자(!)를 둔뒤 속성명으로 ID를 부여하여 Department요소에 ID라는 속성을 추가시켜 해당 속성값으로 DepartmentID Column내용을 출력하도록 합니다.


이번에는 위 결과에 하위 요소를 하나더 추가시켜 보겠습니다. Explicit option으로 요소를 추가하려면 해당 조회구문을 더 만들고 Union으로 Join하는 형태로 나가야 합니다.

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

DepartmentID Column에 이어 Name Column을 Department 요소의 하위요소로 추가하여 조회 하도록 하였습니다. 이처럼 요소를 늘리려면 그 만큼 Union Join을 통해 각 요소의 추가적인 조회 구분을 더 만들어야 합니다.

두번째 Select문에서는 2 As Tag라고 하였습니다. 말 그대로 해당 Select문에서는 DepartmentID와 Name두개의 Column을 조회하여 각 Tag 요소로 출력하기 때문입니다.

또한 1 As Parent라고 한것은 Name조회한 요소를 Department요소의 하위 요소로 출력할 경우 부모 요소는 Department요소가 되기 때문에 Parent를 1이라고 정의한 것입니다. 반면 첫번째 Select문에서는 여전히 조회하는 Column이 하나이므로 출력할 Tag요소도 하나이기 때문에 1 As tag는 변하지 않으며 DepartmentID에 대한 요소의 부모 요소도  첫번째 Select문에 존재하지 않으므로 부모 요소는 Null을 유지하고 있습니다.

한편 첫번째 Select문에 DepartmentID이후 Null을 조회하도록 한 것은 해당 요소의 내용은 두번재 Select문에서 가져오기 때문입니다. 결국 두번째 Select문에서 가져온 Name column값을 Names라는 요소로 만들고 하위 2번째 요소로 정한다음 Nm속성을 만들어 속성의 값으로 표시([Names!2!Nm])한 것입니다.

두번째 Select문에서 DepartmentID와 Name은 이미 첫번째 Select문에서 각 Column에 대한 요소를 [Names!2!Nm]처럼 정의했기 때문에 또 다시 정의하는 것은 불필요합니다. 왜냐하면 Column의 이름만 써주면 그걸로 충분하기 때문이죠.

Top(3)은 출력되는 부분이 너무 커서 행을 다소 줄이기 위해 추가한 것입니다. 그리고 Order By는 조회할 각 Column을 정렬해 주지 않으면 XML출력시 결과요소가 서로 뒤엉키는 문제가 생기지 않도록 정렬하기 위한 것입니다.


이번에는 Department요소와 Names요소에 이어 Group요소를 하나더 추가해 보겠습니다.

요소를 늘리기 위해 조회 구문을 하나 더 만들어 Union으로 join합니다. 세번째 Select문의 구현 원리도 두번째 설명드린 구현원리와 같습니다.


물론 부모 요소는 반드시 첫번째 Select문의 1이어야할 필요는 없습니다. 필요하다면 바꿀 수 있는데 이런경우 당연히 출력되는 형태도 달라질 것입니다.

Select Top(3) 1 As Tag, Null As Parent, DepartmentID As [Department!1!ID], Null As [Names!2!Nm], Null As [Group!3!Name]
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]
For XML Explicit;

세번째 Select문에서 부모 요소를 1이 아닌 2라고 지정하였습니다. 즉, 두번째 요소인 Names요소를 부모요소로 지정한 것입니다.


이번에는 좀 다르게 요소를 추가하지 않고 Names 요소에다가 Name과 GroupName Column의 내용을 Nm, Group의 두개 속성으로 만들어 표현해 보도록 하겠습니다.

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

이전보다 Select문이 하나 더 줄었습니다. 기존에는 Department와 Names 그리고 Group 이렇게 3개의 요소를 출력하려 했으므로 세개의 Select문을 Union Join해야 했지만 이번에는 기존 Group요소를 없애고 대신 해당 Group내용을 Name요소에 통합하여 두개의 속성으로 만들고 출력하기 때문에(출력할 요소가 Department와 Name두개 이기 때문에) Join될 Select구문도 두개면 충분한 것입니다.

먼저 첫번째 Select문에서 두번째 조회 column부분에 [Name!2!Nm]이라고 정의하면 Name요소가 만들어질 것이고 그 이후에 만들어진 Name요소에다가 속성만을 추가할 것이므로 세번째는 요소명과 출력될 요소의 차수를 똑같이 써서 추가할 속성명만 [Names!2!Group]처럼 지정해 주면 됩니다.

물론 두번째 Select문에서는 Group속성에 표시할 Column(예제에서는 GroupName)을 지정해 줘야 하겠지요.


Names요소에 Nm과 Group속성 두개가 추가되고 각각의 속성에 Name과 GroupName내용을 표시하고 있습니다.

이제까지 예제에서는 모두 속성을 통해 값을 표시하고 있지만 이렇게 하지 않고 요소 자체에다가 해당 내용이 보여지도록 지정할 수도 있습니다.

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

요소 지시자에 !로 구분하여 Element를 추가하였습니다. 첫번째 Select문에서 두번재와 세번째 Column조회 부분을 주목해 주십시오.


각 Column의 값이 속성이 아닌 Tag 요소를 통해 표시됨을 확인할 수 있습니다.

참고:
출력하려는 XML의 각 요소에 ID를 지정하고자 하신다면 Element를 지정한 것과 같이 ID를 지정하시면 됩니다. 또한 조회하는 Column에 Null이 있다면 Element대신 Elementxsinil을 사용하시면 Null이 있는 Column도 조회가 가능합니다.
0 0