TSQL FOR XML PATH 属性 On , Type [英] TSQL FOR XML PATH Attribute On , Type
问题描述
基本上我需要从数据库中输出单值和多值字段.数据的大多数用户都不是技术人员,如果他们能够阅读 XML 并理解它,那就太好了.在另一篇文章的帮助下,我几乎一路走到了那里.
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.
我遇到的问题是元素名称不能有任何空格.需要包含可能有空格的显示名称.例如 MVtext displayName="Multi Value Text".如果正确答案是内联模式或其他方法,请告诉我.这不像我可以发布静态模式,因为应用程序可以动态添加字段.并且字段可以随着报告的类型而改变.这些字段在一个表中,我阅读了该表来构建选择.
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.
如何传达可能有元素空格的显示名称?SQL 2008 R2.
How can I communicate a display name that may have spaces for the elements? 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')
产生:
<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>
我累了
SELECT [value] AS 'value', 'MV test' as 'dispName'
但是我明白了
<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'抛出执行错误行标记省略(空行标记名称)不能与以属性为中心的 FOR XML 序列化一起使用
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
期望的输出:
<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>
解决方案:
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')
解决方案输出:
<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.
尝试了一个 Case 语句,但即使我将值设置为 '' 它也会显示该元素.
Tried a Case statement but even if I set the value to '' it displays the element.
,[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')
推荐答案
不确定我明白你想要什么,如果没有你的表格很难用你的代码做一些事情,所以我创建了一个我认为正在做的示例你已经有了.
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.
设置表格和数据:
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')
结果:
<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>
为了让您改用此查询:
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')
这篇关于TSQL FOR XML PATH 属性 On , Type的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!