TSQL FOR XML PATH 属性 On , Type
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8492938/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL FOR XML PATH Attribute On , Type
提问by paparazzo
Basically I need to output both single value and muti value fields from a database. Most users of the data are not technical and it would be nice if they could read the XML and make sense of it. With help from another post I got almost all the way there.
基本上我需要从数据库中输出单值和多值字段。数据的大多数用户都不是技术人员，如果他们能够阅读 XML 并理解它，那就太好了。在另一篇文章的帮助下，我几乎一路走到了那里。
The problem I have is that the element name cannot have any spaces. Need to include a display name that may have spaces. For example MVtext displayName="Multi Value Text". If the right answer is an inline schema or another approach please let me know. It is not like I can a publish static schema as the application can dynamically add fields. And the fields can change with the type of report. The fields are in a table and I read that table to build the select.
我遇到的问题是元素名称不能有任何空格。需要包含可能有空格的显示名称。例如 MVtext displayName="Multi Value Text"。如果正确答案是内联模式或其他方法，请告诉我。这不像我可以发布静态模式，因为应用程序可以动态添加字段。并且字段可以随着报告的类型而改变。这些字段在一个表中，我阅读了该表来构建选择。
How can I communicate a display name that may have spaces for the elements? SQL 2008 R2.
如何传达可能有元素空格的显示名称？SQL 2008 R2。
SELECT top 4
[sv].[sID] AS '@sID'
,[sv].[sParID] AS '@sParID'
,[sv].[docID] AS 'docID'
,[sv].addDate as 'addDate'
,(SELECT [value] AS 'value'
FROM [docMVtext] as [mv]
WHERE [mv].[sID] = [sv].[sID]
AND [mv].[fieldID] = '113'
ORDER BY [mv].[value]
FOR XML PATH (''), type
) AS "To"
,(SELECT [value] AS 'value'
FROM [docMVtext] as [mv]
WHERE [mv].[sID] = [sv].[sID]
AND [mv].[fieldID] = '130'
ORDER BY [mv].[value]
FOR XML PATH (''), type
) AS "MVtest"
FROM [docSVsys] as [sv]
WHERE [sv].[sID] >= '57'
ORDER BY
[sv].[sParID], [sv].[sID]
FOR XML PATH('Document'), root('Documents')
Produces:
产生：
<Documents>
<Document sID="57" sParID="57">
<docID>3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA</docID>
<addDate>2011-10-28T12:26:00</addDate>
<To>
<value>Frank Ermis</value>
<value>Keith Holst</value>
<value>Mike Grigsby</value>
</To>
<MVtest>
<value>MV test 01</value>
<value>MV test 02</value>
<value>MV test 03</value>
<value>MV test 04</value>
</MVtest>
</Document>
<Document sID="58" sParID="57">
<docID>3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA.1</docID>
<addDate>2011-10-28T12:26:00</addDate>
</Document>
<Document sID="59" sParID="59">
<docID>3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA</docID>
<addDate>2011-10-28T12:26:00</addDate>
<To>
<value>Vladimir Gorny</value>
</To>
</Document>
<Document sID="60" sParID="59">
<docID>3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA.1</docID>
<addDate>2011-10-28T12:26:00</addDate>
</Document>
</Documents>
I tired
我累了
SELECT [value] AS 'value', 'MV test' as 'dispName'
But I get
但我得到
<MVtest>
<value>MV test 01</value>
<dispName>MV test</dispName>
<value>MV test 02</value>
<dispName>MV test</dispName>
<value>MV test 03</value>
<dispName>MV test</dispName>
<value>MV test 04</value>
<dispName>MV test</dispName>
</MVtest>
SELECT [value] AS 'value', 'MV test' as '@dispName' Throws a execution error Row tag omission (empty row tag name) cannot be used with attribute-centric FOR XML serialization
SELECT [value] AS 'value', 'MV test' as '@dispName' 引发执行错误行标记遗漏（空行标记名称）不能与以属性为中心的 FOR XML 序列化一起使用
Desired output:
期望的输出：
<Documents>
<Document sID="57" sParID="57">
<docID>3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA</docID>
<addDate>2011-10-28T12:26:00</addDate>
<To>
<value>Frank Ermis</value>
<value>Keith Holst</value>
<value>Mike Grigsby</value>
</To>
<MVtest dispName="Multi Value Text">
<value>MV test 01</value>
<value>MV test 02</value>
<value>MV test 03</value>
<value>MV test 04</value>
</MVtest>
</Document>
</Documents>
Solution:
解决方案：
SELECT top 4
[sv].[sID] AS '@sID'
,[sv].[sParID] AS '@sParID'
,'SV' as 'docID/@SVMV'
,[sv].[docID] AS 'docID'
,'SV' as 'addDate/@SVMV'
,[sv].addDate as 'addDate'
,'Email To' as 'To/@DisplayName'
,'MV' as 'To/@SVMV'
,(SELECT [value] AS 'value'
FROM [docMVtext] as [mv]
WHERE [mv].[sID] = [sv].[sID]
AND [mv].[fieldID] = '113'
ORDER BY [mv].[value]
FOR XML PATH (''), type
) AS "To"
,'Multi Value Text Sample' as 'MVtext130/@DisplayName'
,'MV' as 'MVtext130/@SVMV'
,(SELECT [value] AS 'value'
FROM [docMVtext] as [mv]
WHERE [mv].[sID] = [sv].[sID]
AND [mv].[fieldID] = '130'
ORDER BY [mv].[value]
FOR XML PATH (''), type
) AS "MVtext130"
FROM [docSVsys] as [sv]
WHERE [sv].[sID] >= '57'
ORDER BY [sv].[sParID], [sv].[sID]
FOR XML PATH('Document'), root('Documents')
Solution output:
解决方案输出：
<Documents>
<Document sID="57" sParID="57">
<docID SVMV="SV">3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA</docID>
<addDate SVMV="SV">2011-10-28T12:26:00</addDate>
<To DisplayName="Email To" SVMV="MV">
<value>Frank Ermis</value>
<value>Keith Holst</value>
<value>Mike Grigsby</value>
</To>
<MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV">
<value>MV test 01</value>
<value>MV test 02</value>
<value>MV test 03</value>
<value>MV test 04</value>
</MVtext130>
</Document>
<Document sID="58" sParID="57">
<docID SVMV="SV">3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA.1</docID>
<addDate SVMV="SV">2011-10-28T12:26:00</addDate>
<To DisplayName="Email To" SVMV="MV" />
<MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV" />
</Document>
<Document sID="59" sParID="59">
<docID SVMV="SV">3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA</docID>
<addDate SVMV="SV">2011-10-28T12:26:00</addDate>
<To DisplayName="Email To" SVMV="MV">
<value>Vladimir Gorny</value>
</To>
<MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV" />
</Document>
<Document sID="60" sParID="59">
<docID SVMV="SV">3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA.1</docID>
<addDate SVMV="SV">2011-10-28T12:26:00</addDate>
<To DisplayName="Email To" SVMV="MV" />
<MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV" />
</Document>
</Documents>
This is a great solution to my question as asked. The one symptom of adding the attribute is that I get the element even if there are zero value rows. Ideally it will not list the element if there are no values.
这是我提出的问题的一个很好的解决方案。添加属性的一个症状是，即使有零值行，我也会得到元素。理想情况下，如果没有值，它不会列出元素。
Tried a Case statement but even if I set the value to '' it displays the element.
尝试了一个 Case 语句，但即使我将值设置为 '' 它也会显示该元素。
,[MVtext130/@DisplayName] =
Case (select COUNT(*) FROM [docMVtext]
WHERE [docMVtext].[sID] = [sv].[sID]
AND [docMVtext].[fieldID] = '130')
when '0' then ''
else 'Multi Value Text Sample'
end
Updated solution to not list element with no values:
更新了不列出没有值的元素的解决方案：
SELECT top 4
[sv].[sID] AS '@sID'
,[sv].[sParID] AS '@sParID'
,'SV' as 'docID/@SVMV'
,[sv].[docID] AS 'docID'
,'SV' as 'addDate/@SVMV'
,[sv].addDate as 'addDate'
,(select top(1) 'Email To'
from [docMVtext] as C
where C.[sID] = [sv].[sID] and c.fieldID = '113' ) as 'To/@DisplayName'
,(select top(1) 'MV'
from [docMVtext] as C
where C.[sID] = [sv].[sID] and c.fieldID = '113' ) as 'To/@SVMV'
--,'Email To' as 'To/@DisplayName'
--,'MV' as 'To/@SVMV'
,(SELECT [value] AS 'value'
FROM [docMVtext] as [mv]
WHERE [mv].[sID] = [sv].[sID]
AND [mv].[fieldID] = '113'
ORDER BY [mv].[value]
FOR XML PATH (''), type
) AS "To"
,(select top(1) 'Multi Value Text Sample'
from [docMVtext] as C
where C.[sID] = [sv].[sID] and c.fieldID = '130' ) as 'MVtext130/@DisplayName'
,(select top(1) 'MV'
from [docMVtext] as C
where C.[sID] = [sv].[sID] and c.fieldID = '130' ) as 'MVtext130/@SVMV'
--,'Multi Value Text Sample' as 'MVtext130/@DisplayName'
--,'MV' as 'MVtext130/@SVMV'
,(SELECT [value] AS 'value'
FROM [docMVtext] as [mv]
WHERE [mv].[sID] = [sv].[sID]
AND [mv].[fieldID] = '130'
ORDER BY [mv].[value]
FOR XML PATH (''), type
) AS "MVtext130"
FROM [docSVsys] as [sv]
WHERE [sv].[sID] >= '57'
ORDER BY [sv].[sParID], [sv].[sID]
FOR XML PATH('Document'), root('Documents')
回答by Mikael Eriksson
Not sure I understand what you want and it is hard to do something with your code without the tables you have so I created a sample that I believe is doing what you already have.
不确定我是否理解您想要什么，如果没有您拥有的表，很难用您的代码做一些事情，所以我创建了一个示例，我相信它正在做您已经拥有的事情。
Setup table and data:
设置表和数据：
declare @Main table
(
MainID int identity,
Value int
)
declare @Child table
(
ChildID int identity,
MainID int,
Value int
)
insert into @Main values (10),(20),(30)
insert into @Child values (1,100),(2,200),(2,210)
The query that does approximately the same as yours do already.
与您的查询大致相同的查询。
select M.MainID as '@MainID',
M.Value as 'MainValue',
(select C.Value as ChildValue
from @Child as C
where C.MainID = M.MainID
for xml path(''), type) as Child
from @Main as M
for xml path('Document'), root('Documents')
Result:
结果：
<Documents>
<Document MainID="1">
<MainValue>10</MainValue>
<Child>
<ChildValue>100</ChildValue>
</Child>
</Document>
<Document MainID="2">
<MainValue>20</MainValue>
<Child>
<ChildValue>200</ChildValue>
<ChildValue>210</ChildValue>
</Child>
</Document>
<Document MainID="3">
<MainValue>30</MainValue>
</Document>
</Documents>
I believe that your expected output would be something like this with a display name attribute on the child nodes.
我相信您的预期输出将是这样的，在子节点上有一个显示名称属性。
<Documents>
<Document MainID="1">
<MainValue>10</MainValue>
<Child DisplayName="Child Display Name">
<ChildValue>100</ChildValue>
</Child>
</Document>
<Document MainID="2">
<MainValue>20</MainValue>
<Child DisplayName="Child Display Name">
<ChildValue>200</ChildValue>
<ChildValue>210</ChildValue>
</Child>
</Document>
<Document MainID="3">
<MainValue>30</MainValue>
</Document>
</Documents>
To get that you use this query instead:
要获得它，请改用此查询：
select M.MainID as '@MainID',
M.Value as 'MainValue',
(select top(1) 'Child Display Name'
from @Child as C
where C.MainID = M.MainID) as 'Child/@DisplayName',
(select C.Value as ChildValue
from @Child as C
where C.MainID = M.MainID
for xml path(''), type) as Child
from @Main as M
for xml path('Document'), root('Documents')
