Dela via

Exempel: Hämta information om anställda

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

I det här exemplet hämtas ett medarbetar-ID och medarbetarens namn för varje anställd. AdventureWorks2022 I databasen kan employeeID hämtas från kolumnen BusinessEntityID i tabellen Employee. Medarbetarnamn kan hämtas från tabellen Person. Kolumnen BusinessEntityID kan användas för att koppla tabellerna.

Anta att du vill att FOR XML EXPLICIT-transform ska generera XML så som visas i följande exempel:

<Employee EmpID="1" >
  <Name FName="Ken" LName="Sánchez" />
</Employee>
...

Eftersom det finns två nivåer i hierarkin skulle du skriva två SELECT frågor och tillämpa UNION ALL. Det här är den första frågan som hämtar värden för elementet <Employee> och dess attribut. Frågan tilldelar 1 som Tag värde för elementet <Employee> och NULL som Parent, eftersom det är elementet på den översta nivån.

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;

Det här är den andra frågan. Den hämtar värden för elementet <Name> . Det tilldelar 2 som Tag värde för elementet <Name> och 1 som taggvärde som Parent identifierar <Employee> som överordnat.

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;

Du kombinerar dessa frågor med UNION ALL, tillämpar FOR XML EXPLICIToch anger den obligatoriska ORDER BY satsen. Du måste sortera raduppsättningen först BusinessEntityID efter och sedan efter namn så att NULL-värdena i namnet visas först. Genom att köra följande fråga utan FOR XML-satsen kan du se att den universella tabellen har genererats.

Det här är den sista frågan:

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;

Detta är det partiella resultatet:

<Employee EmpID="1">
  <Name FName="Ken" LName="Sánchez" />
</Employee>
<Employee EmpID="2">
  <Name FName="Terri" LName="Duffy" />
</Employee>
...

Den första SELECT anger namn på kolumner i den resulterande raduppsättningen. Dessa namn bildar två kolumngrupper. Den grupp som har Tag värdet 1 i kolumnnamnet identifierar Employee som ett element och EmpID som attribut. Den andra kolumngruppen har Tag värdet 2 i kolumnen och identifierar <Name> som elementet och FName och LName som attribut.

I följande tabell visas den partiella raduppsättningen som genereras av frågan:

Tagg Förälder Anställd!1!EmpID Namn!2!Förnamn Namn!2!Efternamn
1 NOLL 1 NOLL NOLL
2 1 1 Ken Sánchez
1 NOLL 2 NOLL NOLL
2 1 2 Terri Duffy
1 NOLL 3 NOLL NOLL
2 1 3 Roberto Tamburello
... ... ... ... ...

Så här bearbetas raderna i den universella tabellen för att skapa det resulterande XML-trädet:

Den första raden identifierar Tag värdet 1. Därför identifieras den kolumngrupp som har Tag värdet 1 . Employee!1!EmpID Den här kolumnen identifierar Employee som elementnamnet. Ett <Employee> element skapas sedan som har EmpID attribut. Motsvarande kolumnvärden tilldelas till dessa attribut.

Den andra raden har Tag värdet 2. Därför identifieras den kolumngrupp som har Tag värdet 2 i kolumnnamnet , Name!2!FName, Name!2!LName. Dessa kolumnnamn identifierar sig Name som elementnamn. Ett <Name> element skapas som har FName och LName attribut. Motsvarande kolumnvärden tilldelas sedan till dessa attribut. Den här raden identifierar 1 sig som Parent. Det underordnade elementet läggs till i föregående <Employee> element.

Den här processen upprepas för resten av raderna i raduppsättningen. Observera vikten av att ordna raderna i den universella tabellen så att FÖR XML EXPLICIT kan bearbeta raduppsättningen i ordning och generera den XML du vill ha.

Se även