예: 직원 정보 검색
적용 대상: SQL Server
Azure SQL Database
Azure SQL Managed Instance
다음은 각 직원의 직원 ID와 직원 이름을 검색하는 예제입니다. AdventureWorks2022
데이터베이스에서 employeeID는 Employee 테이블의 BusinessEntityID 열에서 가져올 수 있습니다. 직원 이름은 Person 테이블에서 가져올 수 있습니다. BusinessEntityID 열을 사용하여 테이블을 조인할 수 있습니다.
다음 샘플과 같이 FOR XML EXPLICIT 변환에서 XML을 생성하도록 가정합니다.
<Employee EmpID="1" >
<Name FName="Ken" LName="Sánchez" />
</Employee>
...
계층 구조에는 두 개의 수준이 있으므로 두 개의 SELECT
쿼리를 작성하고 UNION ALL을 적용합니다. <Employee>
요소 및 해당 특성에 대한 값을 검색하는 첫 번째 쿼리입니다. 쿼리는 최상위 요소이므로 1
요소의 Tag
값으로 <Employee>
를, Parent
로 NULL을 할당합니다.
SELECT 1 as Tag,
NULL as Parent,
E.BusinessEntityID AS [Employee!1!EmpID],
NULL as [Name!2!FName],
NULL as [Name!2!LName]
FROM HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON E.BusinessEntityID = P.BusinessEntityID;
이는 두 번째 쿼리입니다. <Name>
요소의 값을 검색합니다. 2
요소의 Tag
값으로 <Name>
, 부모로 1
를 식별하는 Parent
태그 값으로 <Employee>
를 할당합니다.
SELECT 2 as Tag,
1 as Parent,
E.BusinessEntityID,
FirstName,
LastName
FROM HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON E.BusinessEntityID = P.BusinessEntityID;
이러한 쿼리를 UNION AL
L과 결합하고, FOR XML EXPLICIT
를 적용하고, 필요한 ORDER BY
절을 지정합니다. 이름에 NULL 값이 먼저 표시되도록 BusinessEntityID
를 기준으로 행먼저 정렬한 다음 이름을 기준으로 행 집합을 정렬해야 합니다. FOR XML 절 없이 다음 쿼리를 실행하면 생성된 유니버설 테이블을 볼 수 있습니다.
다음은 최종 쿼리입니다.
SELECT 1 as Tag,
NULL as Parent,
E.BusinessEntityID as [Employee!1!EmpID],
NULL as [Name!2!FName],
NULL as [Name!2!LName]
FROM HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON E.BusinessEntityID = P.BusinessEntityID
UNION ALL
SELECT 2 as Tag,
1 as Parent,
E.BusinessEntityID,
FirstName,
LastName
FROM HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON E.BusinessEntityID = P.BusinessEntityID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT;
다음은 결과의 일부입니다.
<Employee EmpID="1">
<Name FName="Ken" LName="Sánchez" />
</Employee>
<Employee EmpID="2">
<Name FName="Terri" LName="Duffy" />
</Employee>
...
첫 번째 SELECT
는 결과 행 집합에 대해 열 이름을 지정합니다. 이러한 이름은 두 개의 열 그룹을 형성합니다. 열 이름에 Tag
값 1
이 있는 그룹은 Employee
를 요소로 EmpID
를 특성으로 식별합니다. 다른 열 그룹에는 열의 Tag
값 2
이 있으며 <Name>
을 요소 및 FName
으로, 특성으로 LName
을 식별합니다.
다음 테이블에서는 쿼리에서 생성된 부분 행 집합을 보여 줍니다.
태그 | 부모 | Employee!1!EmpID | Name!2!FName | Name!2!LName |
---|---|---|---|---|
1 | NULL | 1 | NULL | NULL |
2 | 1 | 1 | Ken | Sánchez |
1 | NULL | 2 | NULL | NULL |
2 | 1 | 2 | Terri | Duffy |
1 | NULL | 3 | NULL | NULL |
2 | 1 | 3 | Roberto | Tamburello |
... | ... | ... | ... | ... |
유니버설 테이블의 행을 처리하여 결과 XML 트리를 생성하는 방법은 다음과 같습니다.
첫 번째 행은 Tag
값을 1
식별합니다. 따라서 Tag
값 1
이 있는 열 그룹은 Employee!1!EmpID
로 식별됩니다. 이 열은 Employee
를 요소 이름으로 식별합니다. 그런 다음 <Employee>
특성이 있는 EmpID
요소가 만들어집니다. 해당 열 값은 이러한 특성에 할당됩니다.
두 번째 행에는 Tag
값 2
가 포함됩니다. 따라서 열 이름, Name!2!FName
, Name!2!LName
,에 Tag
값 2
이 있는 열 그룹이 식별됩니다. 이러한 열 이름은 요소 이름으로 Name
을 식별합니다. FName
및 LName
특성이 있는 <Name>
요소가 만들어집니다. 그런 다음 해당 열 값이 이러한 특성에 할당됩니다. 이 행은 1
로 Parent
을 식별합니다. 이 요소 자식은 이전 <Employee>
요소에 추가됩니다.
이 프로세스는 행 집합의 나머지 행에 대해 반복됩니다. FOR XML EXPLICIT에서 행 집합을 순서대로 처리하고 원하는 XML을 생성하기 위해서는 범용 테이블의 행을 정렬하는 것이 중요합니다.