以 XML 格式检索 SQL 数据时出现问题

Question

我在使用旨在将结果数据返回为 XML 的 SQL 查询时遇到问题。下面是一个代码转储，向您展示所有发生的事情：

SQL 查询(注意：表名和列名已编辑(

with resultdata as
(
SELECT 
(
select * from Table1 (nolock)
where column1 = 99999 and column2 = -1 for xml auto, type
) as tabledata 
UNION ALL
SELECT
(   
select * from Table2 (nolock)
where column1 = 99999 and column2 = -1 for xml auto, type 
)
UNION ALL
SELECT
(
select * from Table3 (nolock)
where column1 = 99999 and column2 = -1 for xml auto, type
)
UNION ALL
SELECT 
(
select * from Table4 (nolock)
where column1 = 99999 and column2 = -1 for xml auto, type
)
UNION ALL
SELECT 
(
select * from Table5 (nolock)
where column1 = 99999 and column2 = -1 for xml auto, type
)
UNION ALL
SELECT 
(
select * from Table6 (nolock)
where column1 = 99999 and column2 = -1 for xml auto, type
)
UNION ALL
SELECT 
(
select * from Table7 (nolock)
where column1 = 99999 and column2 = -1 for xml auto, type
)
UNION ALL
SELECT 
(
select * from Table8 (nolock)
where column1 = 99999 and column2 = -1 for xml auto, type
)
)
select * from resultdata result for xml auto, elements

这将返回如下所示的 XML 结果(大多数 XML 已编辑，注释是实际数据所在的位置(：

<result>
<tabledata>
<!--Table1 results-->
</tabledata>
</result>
<result>
<tabledata>
<!--Table2 results-->
</tabledata>
</result>
<result>
<tabledata>
<!--Table3 results-->
</tabledata>
</result>
<result>
<tabledata>
<!--Table4 results-->
</tabledata>
</result>
<result>
<tabledata>
<!--Table5 results-->
</tabledata>
</result>
<result>
<tabledata>
<!--Table6 results-->
</tabledata>
</result>
<result>
<tabledata>
<!--Table7 results-->
</tabledata>
</result>
<result>
<tabledata>
<!--Table8 results-->
</tabledata>
</result>

显然这是格式错误的XML，但我似乎无法对其进行返工，以便它以正确的格式为我提供结果，如下所示：

<result>
<tabledata>
<!--Table1 results-->
</tabledata>
<tabledata>
<!--Table2 results-->
</tabledata>
<tabledata>
<!--Table3 results-->
</tabledata>
<tabledata>
<!--Table4 results-->
</tabledata>
<tabledata>
<!--Table5 results-->
</tabledata>
<tabledata>
<!--Table6 results-->
</tabledata>
<tabledata>
<!--Table7 results-->
</tabledata>
<tabledata>
<!--Table8 results-->
</tabledata>
</result>

作为旁注，这是对我们现有查询的重写，这就是为什么语法大多是这样。我的一位同事开始重新设计它，基本上给了我我在这里发布的内容，但是我一直在努力尝试使用他的更改来使其工作。如果我需要报废它并从头开始，我这样做没有问题。

另外，作为另一个说明，我希望它看起来像的 XML 必须是这样的，因为它最终由我无法控制的 XSLT 使用。此查询的旧版本将每个 tabledata 元素作为不同的列名返回，然后在调用它的 C# 代码中替换了一个字符串。在此 XML(可以是 100k+ 行(上进行多个字符串替换存在性能问题，因此答案是更改查询以按我们需要的方式返回数据。

Answer 1

只是另一种选择。 这将返回您想要的结果。

Select (select * from Table1 (nolock) where column1 = 99999 and column2 = -1 for XML auto, type, root('tabledata') )
,(Select * from Table2 (nolock) where column1 = 99999 and column2 = -1 for XML auto, type, root('tabledata') )
,(Select * from Table3 (nolock) where column1 = 99999 and column2 = -1 for XML auto, type, root('tabledata') )
For XML Path(''),Root('results')

Answer 2

在 SQL Server 中，XML 变量、列或 FOR XML 查询结果表示一个"XML 片段"，没有单个顶级元素。

对于 XML 查询 如果需要 XML 文档(具有顶级根元素(，请为 FOR XML 添加 ROOT 参数。

Answer 3

如果我正确理解了您的问题，您应该将每个表的查询合并在一起，然后应用 XML 格式。喜欢这个。。。

WITH resultdata AS
(
SELECT (
SELECT *
FROM   (
SELECT *
FROM   Table1 (NOLOCK)
WHERE  column1 = 99999
AND column2 = -1
UNION ALL
SELECT *
FROM   Table2 (NOLOCK)
WHERE  column1 = 99999
AND column2 = -1
UNION ALL
SELECT *
FROM   Table3 (NOLOCK)
WHERE  column1 = 99999
AND column2 = -1
UNION ALL
SELECT *
FROM   Table4 (NOLOCK)
WHERE  column1 = 99999
AND column2 = -1
UNION ALL
SELECT *
FROM   Table5 (NOLOCK)
WHERE  column1 = 99999
AND column2 = -1
UNION ALL
SELECT *
FROM   Table6 (NOLOCK)
WHERE  column1 = 99999
AND column2 = -1
UNION ALL
SELECT *
FROM   Table7 (NOLOCK)
WHERE  column1 = 99999
AND column2 = -1
UNION ALL
SELECT *
FROM   Table8 (NOLOCK)
WHERE  column1 = 99999
AND column2 = -1
) AS x
FOR XML AUTO, TYPE
) AS tabledata
)
SELECT * FROM resultdata AS result FOR XML AUTO, ELEMENTS;

Answer 4

以下解决方案怎么样，它允许从具有不同结构的数据库表中构造最终的XML。

.SQL

-- DDL and sample data population, start
DECLARE @tbl1 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, city VARCHAR(30));
INSERT INTO @tbl1
VALUES
('Miami')
, ('Orlando');
DECLARE @tbl2 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, [state] VARCHAR(30));
INSERT INTO @tbl2
VALUES
('Florida')
, ('Texas');
-- DDL and sample data population, end
WITH resultdata (tabledata) AS
(
SELECT 
(
SELECT * FROM @tbl1
FOR XML PATH('row'), TYPE, ROOT('tbl1')
) AS [tbl1]
UNION ALL
SELECT
(   
SELECT * FROM @tbl2
FOR XML PATH('row'), TYPE, ROOT('tbl2')
)
)
SELECT * 
FROM resultdata
FOR XML PATH(''), TYPE, ROOT('result');

输出 XML

<result>
<tabledata>
<tbl1>
<row>
<ID>1</ID>
<city>Miami</city>
</row>
<row>
<ID>2</ID>
<city>Orlando</city>
</row>
</tbl1>
</tabledata>
<tabledata>
<tbl2>
<row>
<ID>1</ID>
<state>Florida</state>
</row>
<row>
<ID>2</ID>
<state>Texas</state>
</row>
</tbl2>
</tabledata>
</result>