需要使用来自 SQL 查询的值更新 XML 字符串中的多个节点 [英] Need to update multiple nodes in a XML string using values from an SQL query
问题描述
我有一个SQL存储过程局部变量@DocList(声明@DocList XML)其中包含以下 XML 数据:
I have a SQL stored procedure local variable @DocList (Declare @DocList XML) which contains the follwing XML data:
<JobList ListItems="7">
<Job JobFriendlyName="EMAIL INVOICES">
<DocumentList>
<Document Doc="1" ID="5280301.2019050148902.00020" Date="05-03-2019" Status="NEW" />
<Document Doc="2" ID="5280301.2019050148902.00022" Date="05-03-2019" Status="NEW" />
<Document Doc="3" ID="5280301.2019050148902.00023" Date="05-03-2019" Status="NEW" />
<Document Doc="4" ID="5280301.2019050104301.00055" Date="05-02-2019" Status="NEW" />
<Document Doc="5" ID="5280301.2019050104301.00056" Date="05-02-2019" Status="NEW" />
</DocumentList>
</Job>
<Job JobFriendlyName="INVOICES">
<DocumentList>
<Document Doc="6" ID="5280300.2019050148901.00001" Date="05-03-2019" Status="NEW" />
<Document Doc="7" ID="5280300.2019050148901.00002" Date="05-03-2019" Status="NEW" />
</DocumentList>
</Job>
</JobList>
我还有一个 SQL 表DocAccess"，其中包含 0 行或更多行，DocIDNumber 与 XML 中匹配的ID"属性值相关:
I also have an SQL table "DocAccess" which contains 0 or more rows with DocIDNumber the correlate to matching "ID" attribute values in the XML:
TABLE [tblDocAccess]
(
[Key] varachar(10),
[DocIDNumber] [varchar](35),
[DocLastOpenDtg] [smalldatetime]
)
我想应用查询select [DocIDNumber] from [tblDocAccess] where [Key] = {some任意值}"针对@DocList 中的 XML，将每个节点的属性Status"值从NEW"修改为OLD"属性ID"值匹配返回的 [DocIdNumber] 值.
I want to apply query "select [DocIDNumber] from [tblDocAccess] where [Key] = {some arbitrary value}" against the XML in @DocList to modify attribute "Status" value from "NEW" to "OLD" for each node where attribute "ID" values matches a returned [DocIdNumber] values.
我知道我可以为 select 语句创建一个游标，然后循环定位/更新任何匹配节点/属性值，但这确实说明似乎有效.
I know I could create a cursor fror the select statement, then loop to locate/update any matching node/attribute value, but that does note seem efficient.
任何建议的帮助将不胜感激.
Any assistance of suggestions would be appreciated.
====================================
===================================
后续问题:使用上面在@DocList 中显示的 XML 文档和另一个包含要搜索的值的局部变量 @SearchID varchar(35)，我将如何编码所需的 {While ... Exists ... 设置) 逻辑将 ID 与 @SearchID 中的值匹配的文档的状态设置为OLD".
Follow-up question: Using the XML document shown above in @DocList, and another local variable, @SearchID varchar(35) which contains a value to search for, how would I code the required {While ... Exists ... Set) logic to set Status to "OLD" for the Document with ID matching the value in @SearchID.
请原谅我的无知.我使用 SQL 已经很多年了，但这是我第一次尝试更新现有的 XML 文档.
Please forgive my ignorance here. I have been working with SQL for many years, but this is my first attempt to update an existing XML document.
推荐答案
XML 方法 .modify() 允许一次进行一个更改.这意味着对每个需要的更改使用一个语句.CURSOR 或 WHILE 循环可能是个好主意.
The XML-method .modify() allows for one change at a time. This would mean to use one statement per needed change. A CURSOR or a WHILE loop might be a good idea.
当我尽量避免程序逻辑时，你可以看看这些替代方案:
As I try to avoid procedural logic, you can have a look at these alternatives:
一种方法是将整个内容切碎并从头开始重新创建:
One approach was to shred the whole thing and recreate it from scratch:
首先我创建一个模型来模拟你的情况
First I create a mockup to simulate your situation
DECLARE @DocList XML=
N'<JobList ListItems="7">
<Job JobFriendlyName="EMAIL INVOICES">
<DocumentList>
<Document Doc="1" ID="5280301.2019050148902.00020" Date="05-03-2019" Status="NEW" />
<Document Doc="2" ID="5280301.2019050148902.00022" Date="05-03-2019" Status="NEW" />
<Document Doc="3" ID="5280301.2019050148902.00023" Date="05-03-2019" Status="NEW" />
<Document Doc="4" ID="5280301.2019050104301.00055" Date="05-02-2019" Status="NEW" />
<Document Doc="5" ID="5280301.2019050104301.00056" Date="05-02-2019" Status="NEW" />
</DocumentList>
</Job>
<Job JobFriendlyName="INVOICES">
<DocumentList>
<Document Doc="6" ID="5280300.2019050148901.00001" Date="05-03-2019" Status="NEW" />
<Document Doc="7" ID="5280300.2019050148901.00002" Date="05-03-2019" Status="NEW" />
</DocumentList>
</Job>
</JobList>';
DECLARE @mockupDocAccess TABLE
(
[Key] varchar(10),
[DocIDNumber] [varchar](35),
[DocLastOpenDtg] [smalldatetime]
);
INSERT INTO @mockupDocAccess VALUES('SomeKey','5280301.2019050148902.00022',GETDATE()) --Doc 2
,('SomeKey','5280301.2019050104301.00055',GETDATE()) --Doc 4
,('SomeKey','5280300.2019050148901.00001',GETDATE()) --Doc 6
,('OtherKey','5280301.2019050104301.00056',GETDATE()); --Doc 5
--现在我们可以从 XML 中读取所有值并在使用 CASE 将所需的 status 值设置为 OLD:
--Now we can read all values from the XML and re-create the XML after using CASE to set the needed status values to OLD:
DECLARE @Key VARCHAR(10)='SomeKey';
WITH AllEmailInvoices AS
(
SELECT d.value('@Doc','int') AS Doc
,d.value('@ID','nvarchar(35)') AS ID
,d.value('@Date','nvarchar(10)') AS [Date] --unconverted
,CASE WHEN EXISTS(SELECT 1 FROM @mockupDocAccess da WHERE da.DocIDNumber=d.value('@ID','nvarchar(35)') AND da.[Key]=@Key) THEN 'OLD' ELSE d.value('@Status','nvarchar(10)') END AS [Status]
FROM @DocList.nodes('/JobList/Job[@JobFriendlyName="EMAIL INVOICES"]/DocumentList/Document') A(d)
)
,AllInvoices AS
(
SELECT d.value('@Doc','int') AS Doc
,d.value('@ID','nvarchar(35)') AS ID
,d.value('@Date','nvarchar(10)') AS [Date] --unconverted
,CASE WHEN EXISTS(SELECT 1 FROM @mockupDocAccess da WHERE da.DocIDNumber=d.value('@ID','nvarchar(35)') AND da.[Key]=@Key) THEN 'OLD' ELSE d.value('@Status','nvarchar(10)') END AS [Status]
FROM @DocList.nodes('/JobList/Job[@JobFriendlyName="INVOICES"]/DocumentList/Document') A(d)
)
SELECT @DocList.value('(/JobList/@ListItems)[1]','int') AS [@ListItems]
,(
SELECT 'EMAIL INVOICES' AS [@JobFriendlyName]
,(
SELECT Doc AS [@Doc]
,ID AS [@ID]
,[Date] AS [@Date]
,[Status] AS [@Status]
FROM AllEmailInvoices
FOR XML PATH('Document'),ROOT('DocumentList'),TYPE
)
FOR XML PATH('Job'),TYPE
)
,(
SELECT 'INVOICES' AS [@JobFriendlyName]
,(
SELECT Doc AS [@Doc]
,ID AS [@ID]
,[Date] AS [@Date]
,[Status] AS [@Status]
FROM AllInvoices
FOR XML PATH('Document'),ROOT('DocumentList'),TYPE
)
FOR XML PATH('Job'),TYPE
)
FOR XML PATH('JobList');
XQuery 和 FLWOR 方法
或者，您可以尝试以下方法:
XQuery and FLWOR approach
Alternatively you can try something along this:
DECLARE @Key VARCHAR(10)='SomeKey';
SELECT
(
SELECT (SELECT DocIDNumber AS ID FROM @mockupDocAccess WHERE [Key]=@Key FOR XML PATH(''),TYPE) DocAccess
,@DocList
FOR XML PATH(''),TYPE
).query
(N'
<JobList> {/JobList/@*}
{
for $j in /JobList/Job
return
<Job> {$j/@*}
{
<DocumentList>
{
for $d in $j/DocumentList/Document
return
<Document Doc="{$d/@Doc}"
ID="{$d/@ID}"
Date="{$d/@Date}"
Status="{if(/DocAccess[ID=$d/@ID]) then "OLD" else xs:string($d/@Status)}" />
}
</DocumentList>
}
</Job>
}
</JobList>
');
首先，我们创建一个 XML，其中包含来自 DocAccess 表的值.这将如下所示:
First we create a XML where we include the values from the DocAccess-table. This will look like this:
<DocAccess>
<ID>5280301.2019050148902.00022</ID>
<ID>5280301.2019050104301.00055</ID>
<ID>5280300.2019050148901.00001</ID>
</DocAccess>
<JobList ListItems="7">
<!-- Your Content here -->
</JobList>
XQuery 将重建文档，但将根据 Status 属性.
The XQuery will rebuild the document but will set the Status-attribute depending on the existance of a corresponding ID element in <DocAccess>.
你可以使用一个
- 每次更改都有单独的语句的 CURSOR，
- 您可以粉碎并重新创建 XML 或
- 您可以使用 XQuery/FLWOR 重新构建 XML.
这取决于您的需求，您喜欢哪种方法.
It depends on your needs, which approach you prefer.
这篇关于需要使用来自 SQL 查询的值更新 XML 字符串中的多个节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
