comment convertir le résultat d'une procédure stockée en xml et l'utiliser comme sous-nœud dans le résultat d'une autre procédure ?

Question

J'ai une procédure stockée nommée

usp_Employee_GetInfo

cette proc retourne un tableau après exécution. comme ci-dessous :

Id Name DeptID
1  AAA  10

Je veux créer une nouvelle procédure qui renvoie du xml après exécution. Et cela inclut le résultat de la procédure ci-dessus comme un sous-nœud comme ci-dessous :

<company Name="XXX">
 <Emp>
   <Id>1</Id>
   <Name>AAA</Name>
   <DeptID>10</DeptID>
 </Emp>
</company>
<company Name="YYY">
 <Emp>
   <Id>2</Id>
   <Name>BBB</Name>
   <DeptID>20</DeptID>
 </Emp>
</company>

Pouvez-vous me suggérer la meilleure façon d'utiliser la procédure existante pour obtenir la sortie requise. Merci.

Answer 1

Vous pouvez déclarer une variable de table. Mettez les résultats de SP dans ce tableau et créez le XML dont vous avez besoin :

DECLARE @emp TABLE (
    Id int,
    [Name] nvarchar(max),
    DeptID int,
    company nvarchar(max)
)

INSERT INTO @emp 
EXEC usp_Employee_GetInfo

--F.e. output of SP is:
--Id    Name    DeptID  company
--1     AAA     10      XXX
--2     BBB     20      YYY 

SELECT  company as 'company/@name',
        emp.Id as 'Emp/id',
        emp.[Name] as 'Emp/Name',
        emp.DeptID as 'Emp/DeptID'
FROM @emp
FOR XML PATH('')

La sortie est prévue :

<company name="XXX" />
<Emp>
  <id>1</id>
  <Name>AAA</Name>
  <DeptID>10</DeptID>
</Emp>
<company name="YYY" />
<Emp>
  <id>2</id>
  <Name>BBB</Name>
  <DeptID>20</DeptID>
</Emp>