拆分XML以将其发送到SQL Server中的存储过程

Question

我有一个关于拆分XML输入参数并将其发送到另一个存储过程(SQL Server(的问题。

我有一个主存储过程，它的输入中有一个XML(见下文(。我希望这个存储过程通过RDHistorique来拆分XML。然后将每个RDHistorique发送到一个子存储过程。

<ArrayOfRDHistorique>
<RDHistorique>
<Id>1955</Id>
<sect_code>08</sect_code> 
<arch_islast>0</arch_islast>  
<ListeEtapes>
<EtapeWorkflowArchive>
<Statut>00</Statut>
<Nom>John DOE</Nom>
<Date>2009-12-31</Date>
<Commentaire />
<Id>1955</Id>
</EtapeWorkflowArchive>
<EtapeWorkflowArchive>
<Statut>02</Statut>
<Nom>John DOE</Nom>
<Date>2009-12-31</Date>
<Commentaire />
<Id>1955</Id>
</EtapeWorkflowArchive>
<EtapeWorkflowArchive>
<Statut>03</Statut>
<Nom>John DOE</Nom>
<Date>2010-06-23</Date>
<Commentaire>Silly comment</Commentaire>
<Id>1955</Id>
</EtapeWorkflowArchive>
<EtapeWorkflowArchive>
<Statut>04</Statut>
<Nom>John DOE</Nom>
<Date>2010-06-23</Date>
<Commentaire>Silly comment</Commentaire>
<Id>1955</Id>
</EtapeWorkflowArchive>
<EtapeWorkflowArchive>
<Statut>05</Statut>
<Nom>John DOE</Nom>
<Date>2010-06-23</Date>
<Commentaire />
<Id>1955</Id>
</EtapeWorkflowArchive>
</ListeEtapes>
</RDHistorique>
<RDHistorique>
<Id>1999</Id>
<sect_code>08</sect_code> 
<arch_islast>0</arch_islast> 
<ListeEtapes>
<EtapeWorkflowArchive>
<Statut>00</Statut>
<Nom>John DOE</Nom>
<Date>2010-12-31</Date>
<Commentaire />
<Id>1999</Id>
</EtapeWorkflowArchive>
<EtapeWorkflowArchive>
<Statut>02</Statut>
<Nom>John DOE</Nom>
<Date>2010-12-31</Date>
<Commentaire />
<Id>1999</Id>
</EtapeWorkflowArchive>
<EtapeWorkflowArchive>
<Statut>03</Statut>
<Nom>NULL</Nom>
<Date>2011-06-29</Date>
<Id>1999</Id>
</EtapeWorkflowArchive>
<EtapeWorkflowArchive>
<Statut>06</Statut>
<Nom>NULL</Nom>
<Commentaire>Silly comment</Commentaire>
<Id>1999</Id>
</EtapeWorkflowArchive>
<EtapeWorkflowArchive>
<Statut>11</Statut>
<Nom>NULL</Nom>
<Commentaire>NULL</Commentaire>
<Id>1999</Id>
</EtapeWorkflowArchive>
</ListeEtapes>
</RDHistorique>
</ArrayOfRDHistorique>

我知道我可以用光标做我想做的事情，但我想用Select来做。我已经可以像这样选择我想要的节点：

SELECT 
b.value('(./Statut/text())[1]','Varchar(50)') as Statut,
b.value('(./Nom/text())[1]','Varchar(50)') as Nom,
b.value('(./Commentaire/text())[1]','Varchar(50)') as Commentaire

FROM @xml.nodes('/ArrayOfRDHistorique/RDHistorique/ListeEtapes/*') as a(b) 

我的问题是：我可以为XML文件中的每个节点调用存储过程吗？

Answer 1

我真的需要迭代方法(过程已经写好了，你做了一些非SQL的工作(，CURSOR是选项：

CREATE PROCEDURE ProcessStatut(@statut varchar(50), @nom varchar(50),@commentaire varchar(50)) AS
BEGIN
SELECT @statut Statut, @nom Nom, @commentaire Commentaire, 'Hello world!' Hello
END

然后：

DECLARE @statut varchar(50), @nom varchar(50),@commentaire varchar(50);
DECLARE c CURSOR FOR
SELECT 
b.value('(./Statut/text())[1]','Varchar(50)') as Statut,
b.value('(./Nom/text())[1]','Varchar(50)') as Nom,
b.value('(./Commentaire/text())[1]','Varchar(50)') as Commentaire
FROM @xml.nodes('/ArrayOfRDHistorique/RDHistorique/ListeEtapes/*') as a(b)
OPEN c
FETCH NEXT FROM c INTO @statut, @nom, @commentaire
WHILE @@FETCH_STATUS = 0  
BEGIN
EXEC ProcessStatut @statut, @nom, @commentaire
FETCH NEXT FROM c INTO @statut, @nom, @commentaire
END
CLOSE c
DEALLOCATE c

每一行都将单独处理。

---

编辑1：若需要传递表，可以声明table类型：

CREATE TYPE StatutList AS TABLE (
Statut varchar(50),
Nom varchar(50),
Commentaire varchar(50)
)
GO
CREATE PROCEDURE ProcessStatutList(@statutList StatutList READONLY) AS
BEGIN
SELECT *, 'I was here!' Hello FROM @statutList
END
GO

然后，使用它一次传递所有项目：

DECLARE @statutList StatutList;
INSERT @statutList
SELECT 
b.value('(./Statut/text())[1]','Varchar(50)') as Statut,
b.value('(./Nom/text())[1]','Varchar(50)') as Nom,
b.value('(./Commentaire/text())[1]','Varchar(50)') as Commentaire
FROM @xml.nodes('/ArrayOfRDHistorique/RDHistorique/ListeEtapes/*') as a(b)
EXEC ProcessStatutList @statutList;