使用自定义属性名称设置多个XML元素的SQL查询

Question

我的要求是通过转换返回结果中多个元素的SQL查询来返回单个xml结果。

因此SQL查询如下，

SELECT 'z_milestonetype' AS 'ColumnValue/@name',  
(select
task_milestone as milestone from PLAN where
request_id='1173' and task_milestone='PC') AS ColumnValue,  null,
'z_actual_finish' AS 'ColumnValue/@name',  
(select effect_dttm as
effect_dttm from PLAN where request_id='1173' and
task_milestone='PC') AS ColumnValue,  null FOR XML PATH ('')

这实际上返回了一个类似下面的XML

<ColumnValue name="z_milestonetype">LM_EQ</ColumnValue>
<ColumnValue name="z_actual_finish">2020-07-15T18:30:00</ColumnValue>

由于结果只检索一个结果，所以这似乎很好，但我的要求是用迭代值设置XML。请参阅下面的XML代码。

<customObjectInstances objectCode="zsinboundtask">
<instance instanceCode="-1" objectCode="zinboundtask">
<CustomInformation>
<ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
<ColumnValue name="page_layout">odf.znecinboundtaskFrame</ColumnValue>
<ColumnValue name="name">-1</ColumnValue>
<ColumnValue name="code">-1</ColumnValue>               
<ColumnValue name="z_milestonetype">TEST2</ColumnValue>
<ColumnValue name="z_finish">2020-09-25T18:30:00</ColumnValue>
</CustomInformation>
</instance>
<instance instanceCode="-1" objectCode="zinboundtask">
<CustomInformation>
<ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
<ColumnValue name="page_layout">odf.znecinboundtaskFrame</ColumnValue>
<ColumnValue name="name">-1</ColumnValue>
<ColumnValue name="code">-1</ColumnValue>                
<ColumnValue name="z_milestonetype">TEST</ColumnValue>
<ColumnValue name="z_finish">2020-09-25T18:30:00</ColumnValue>
</CustomInformation>
</instance>       
………    
</customObjectInstances>

如果我使用与上面相同的查询来设置多行的值，这将显示错误Subquery返回的值超过1。当子查询跟在=、！=、<lt；=>gt；=或者当子查询用作表达式时。

这就是我迄今为止所尝试的。

SELECT
'-1' AS 'instance/@instanceCode', 'zinboundtask' AS 'instance/@objectCode',
null,
null,
'partition_code' AS 'ColumnValue/@name', 
'NIKU.ROOT' AS ColumnValue,
null,
'page_layout' AS 'ColumnValue/@name', 
'odf.zinboundtaskFrame' AS ColumnValue,
null,
'name' AS 'ColumnValue/@name', 
'-1' AS ColumnValue,
null,
'code' AS 'ColumnValue/@name', 
'-1' AS ColumnValue,
null,
'z_milestonetype' AS 'ColumnValue/@name', 
(select task_milestone as milestone from PLAN where request_id='1173') AS ColumnValue,
null,
'z_finish' AS 'ColumnValue/@name', 
(select effect_dttm as effect_dttm from PLAN where request_id='1173') AS ColumnValue,
null
FOR XML PATH ('CustomInformation')

如前所述，

1. 我需要用自定义属性格式化XML
2. 同时，我需要以给定的xml格式显示select查询中的元素

如有任何建议，我们将不胜感激。

Answer 1

您可以使用[text((]标记，如下所示：

select name as '@name' , o.object_id as [text()]
from sys.objects o
for xml path('ColumnValue')

如果你发布了一些样本数据，我相信我们可以绕过你的查询。

Answer 2

在等待DDL和示例数据填充时，这里有一个概念示例。

您只需要添加所有列，添加WHERE子句逻辑等。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, partition_code VARCHAR(30), page_layout VARCHAR(50), z_finish DATETIME);
INSERT INTO @tbl (partition_code, page_layout, z_finish) VALUES
(   'NIKU.ROOT', 'odf.znecinboundtaskFrame', '2020-09-25T18:30:00'),
(   'NIKU.BOOT', 'odf.inboundtaskFrame', '2018-01-25T22:20:00');
-- DDL and sample data population, end
SELECT (
SELECT 'partition_code' as 'ColumnValue/@name', partition_code as [ColumnValue]
, ''
, 'page_layout' as 'ColumnValue/@name', page_layout as [ColumnValue]
, ''
, 'z_finish' as 'ColumnValue/@name', z_finish as [ColumnValue]
FROM @tbl
FOR XML PATH('CustomInformation'), TYPE
).query('<customObjectInstances objectCode="zsinboundtask">
{
for $x in CustomInformation
return <instance instanceCode="-1" objectCode="zinboundtask">
{$x}
</instance>
}
</customObjectInstances>')

输出

<customObjectInstances objectCode="zsinboundtask">
<instance instanceCode="-1" objectCode="zinboundtask">
<CustomInformation>
<ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
<ColumnValue name="page_layout">odf.znecinboundtaskFrame</ColumnValue>
<ColumnValue name="z_finish">2020-09-25T18:30:00</ColumnValue>
</CustomInformation>
</instance>
<instance instanceCode="-1" objectCode="zinboundtask">
<CustomInformation>
<ColumnValue name="partition_code">NIKU.BOOT</ColumnValue>
<ColumnValue name="page_layout">odf.inboundtaskFrame</ColumnValue>
<ColumnValue name="z_finish">2018-01-25T22:20:00</ColumnValue>
</CustomInformation>
</instance>
</customObjectInstances>