SQL Server从JOINed select语句生成XML数据行 [英] SQL Server to produce XML data rows from JOINed select statement
问题描述
在SQL Server 2008中，我有三个表，其设置如下:
I have a three tables in SQL Server 2008 which are setup as follows:
员工表
empid(PK)
1
2
加入了员工奖章
dataId(PK) | empId(FK) | attributeid | attributeVal
10 | 1 | A1 | somevalue1
20 | 1 | A2 | somevalue2
30 | 2 | A1 | somevalue3
40 | 2 | A3 | somevalue4
加入了属性
attributeid | attributeName
A1 | attribute1
A2 | attribute2
A3 | attribute3
我需要将xml数据转换为以下格式
I need to get the xml data out into the following format
<rows>
<row empid="1">
<attribute1>somevalue1</attribute1>
<attribute2>somevalue2</attribute1>
</row>
<row empid="2">
<attribute1>somevalue3</attribute1>
<attribute3>somevalue4</attribute1>
</row>
</rows>
任何人都知道该怎么做吗?
Anyone know how this can be done??
推荐答案
如果您想跳过所有详细信息，只是看到答案，请查看此帖子底部的SQL查询.
If you want to skip all of the gory details and just see an answer, look at the SQL query at the bottom of this posting.
这里的主要挑战是，各种SQL Server FOR XML 选项无法生成所需输出中规定的动态元素名称.因此，我的第一个答案是考虑简单地返回常规的SQL结果集，并让客户端生成XML.这是一个非常简单的流转换.但是，这可能不是您的选择，因此我们继续让SQL Server生成XML.
The main challenge here is that the various SQL Server FOR XML options cannot generate the dynamic element names stipulated in the desired output. Therefore, my first answer is to consider simply returning a conventional SQL result set and having the client generate the XML. It is a very simple streaming transformation. However, this might not be an option for you, so we continue on the path of having SQL Server generate the XML.
我的第二个想法是使用SQL Server的内置XQuery功能来执行转换，因此:
My second thought was to use SQL Server's built-in XQuery functionality to perform the transformation, thus:
/* WARNING: the following SQL does not work */
SELECT
CAST((SELECT * FROM data FOR XML RAW) AS XML)
.query('
<rows>
{
for $empId in distinct-values(/row/@empId)
return
<row empid="{$empId}">
{
for $attr in /row[@empId = $empId]
return
attribute { "attribute" } { $attr/@attributeValue }
}
</row>
}
</rows>
')
A，这行不通. SQL Server抱怨:
Alas, this does not work. SQL Server complains:
Msg 9315, Level 16, State 1, Line 25
XQuery [query()]: Only constant expressions are supported for the name expression
of computed element and attribute constructors.
显然，XQuery实现与 FOR XML 功能具有相同的局限性.所以，我的第二个答案是建议在客户端生成XML :)但是，如果您坚持要从SQL生成XML，那么请系好安全带...
Apparently, the XQuery implementation suffers from the same limitation as the FOR XML features. So, my second answer is to suggest generating the XML on the client side :) But if you insist on generating the XML from SQL, then fasten your seatbelts...
总体策略将是放弃SQL Server的本机工具来生成SQL.相反，我们将使用字符串连接来构建XML文档.如果这种方法令人反感，则可以立即停止阅读:)
The overall strategy is going to be to abandon SQL Server's native facilities for SQL generation. Instead, we are going to build up the XML document using string concatenation. If this approach is offensive, you can stop reading now :)
让我们从生成示例数据集开始进行操作:
Let's start with generating a sample dataset to play with:
SELECT NULL AS empId INTO employee WHERE 1=0
UNION SELECT 1
UNION SELECT 2
SELECT NULL AS dataId, NULL AS empId, NULL AS attributeId, NULL AS attributeVal INTO employeeAttributes WHERE 1=0
UNION SELECT 10, 1, 'A1', 'someValue1'
UNION SELECT 20, 1, 'A2', 'someValue2'
UNION SELECT 30, 2, 'A1', 'someValue3'
UNION SELECT 40, 2, 'A3', 'someValue4 & <>!'
SELECT NULL AS attributeId, NULL AS attributeName INTO attributes WHERE 1=0
UNION SELECT 'A1', 'attribute1'
UNION SELECT 'A2', 'attribute2'
UNION SELECT 'A3', 'attribute3'
请注意，我在提供的示例中更改了last属性的值，以包括一些XML不友好的字符.
Note that I have changed the value of the last attribute in the provided example to include some XML-unfriendly characters.
现在，将基本的SQL查询放在一起以执行必要的联接:
Now, put together a basic SQL query to perform the necessary joins:
SELECT
e.empId
, a.attributeName
, ea.attributeVal
FROM employee AS e
INNER JOIN employeeAttributes AS ea
ON ea.empId = e.empId
INNER JOIN attributes AS a
ON a.attributeId = ea.attributeId
给出以下结果:
empId attributeName attributeVal
1 attribute1 someValue1
1 attribute2 someValue2
2 attribute1 someValue3
2 attribute3 someValue4 & <>!
最后一个属性中的那些有趣的人物会给我们带来麻烦.让我们更改查询以使它们转义.
Those funny characters in the last attribute are going to give us trouble. Let's change the query to escape them.
; WITH
cruftyData AS (
SELECT
e.empId
, a.attributeName
, (SELECT ea.attributeVal AS x FOR XML RAW) AS attributeValXml
FROM employee AS e
INNER JOIN employeeAttributes AS ea
ON ea.empId = e.empId
INNER JOIN attributes AS a
ON a.attributeId = ea.attributeId
)
, data AS (
SELECT
empId
, attributeName
, SUBSTRING(attributeValXml, 9, LEN(attributeValXml)-11) AS attributeVal
FROM cruftyData
)
SELECT * FROM data
有结果:
empId attributeName attributeValXml
1 attribute1 someValue1
1 attribute2 someValue2
2 attribute1 someValue3
2 attribute3 someValue4 & <>!
这确保了属性值现在可以在XML文档中安全地使用.属性名称呢? XML属性名称的规则比元素内容的规则更严格.我们将假定属性名称是有效的XML标识符.如果不是这样，则需要设计一些方案以将数据库中的名称转换为有效的XML名称.这留给读者练习:)
This ensures that attribute values can now be safely used in an XML document. What about attribute names? The rules for XML attribute names are more strict than those for element content. We will assume that the attributes names are valid XML identifiers. If this is not true, then some scheme will need to be devised to convert the names in the database to valid XML names. This is left as an exercise to the reader :)
下一个挑战是确保将每个员工的属性分组在一起，并且我们可以知道何时处于组中的第一个或最后一个值.这是更新的查询:
The next challenge is to ensure that the attributes are grouped together for each employee, and we can tell when we are at the first or last value in a group. Here is the updated query:
; WITH
cruftyData AS (
SELECT
e.empId
, a.attributeName
, (SELECT ea.attributeVal AS x FOR XML RAW) AS attributeValXml
FROM employee AS e
INNER JOIN employeeAttributes AS ea
ON ea.empId = e.empId
INNER JOIN attributes AS a
ON a.attributeId = ea.attributeId
)
, data AS (
SELECT
empId
, attributeName
, SUBSTRING(attributeValXml, 9, LEN(attributeValXml)-11) AS attributeVal
, ROW_NUMBER() OVER (PARTITION BY empId ORDER BY attributeName DESC) AS down
, ROW_NUMBER() OVER (PARTITION BY empId ORDER BY attributeName) AS up
FROM cruftyData
)
SELECT * FROM data ORDER BY 1, 2
唯一的变化是将 down 和 up 列添加到结果集中:
The only change is to add the down and up columns to the result set:
empId attributeName attributeVal down up
1 attribute1 someValue1 2 1
1 attribute2 someValue2 1 2
2 attribute1 someValue3 2 1
2 attribute3 someValue4 & <>! 1 2
我们现在可以确定员工的第一个属性，因为 up 将为 1 .可以使用 down 列以类似的方式标识最后一个属性.
We can now identify the first attribute for an employee because up will be 1. The last attribute can be identified in similar fashion using the down column.
有了所有这些，我们现在可以执行使用字符串连接构建XML结果的烦人的事情.
Armed with all of this, we are now equipped to perform the nasty business of building up the XML result using string concatenation.
; WITH
cruftyData AS (
SELECT
e.empId
, a.attributeName
, (SELECT ea.attributeVal AS x FOR XML RAW) AS attributeValXml
FROM employee AS e
INNER JOIN employeeAttributes AS ea
ON ea.empId = e.empId
INNER JOIN attributes AS a
ON a.attributeId = ea.attributeId
)
, data AS (
SELECT
empId
, attributeName
, SUBSTRING(attributeValXml, 9, LEN(attributeValXml)-11) AS attributeVal
, ROW_NUMBER() OVER (PARTITION BY empId ORDER BY attributeName DESC) AS down
, ROW_NUMBER() OVER (PARTITION BY empId ORDER BY attributeName) AS up
FROM cruftyData
)
, xmlData AS (
SELECT
empId
, up
, CASE WHEN up <> 1 THEN '' ELSE '<row id="' + CAST (empId AS NVARCHAR) + '">' END AS xml1
, '<' + attributeName + '>' + attributeVal + '</' + attributeName + '>' AS xml2
, CASE WHEN down <> 1 THEN '' ELSE '</row>' END AS xml3
FROM data
)
SELECT xml1, xml2, xml3
--SELECT @result = @result + 'wombat' + xmlString
FROM xmlData
ORDER BY empId, up
结果:
xml1 xml2 xml3
<row id="1"> <attribute1>someValue1</attribute1>
<attribute2>someValue2</attribute2> </row>
<row id="2"> <attribute1>someValue3</attribute1>
<attribute3>someValue4 & <>!</attribute3> </row>
剩下的就是将所有行连接在一起，并添加根行标记.由于T-SQL还没有字符串串联聚合函数，因此我们将求助于使用变量作为累加器. 这是最终的查询，它充满了骇人听闻的荣耀:
All that remains is to concatenate all of the rows together, and to add the root rows tags. Since T-SQL does not (yet) have a string concatenation aggregate function, we will resort to using a variable as an accumulator. Here is the final query, in all its hacky glory:
DECLARE @result AS NVARCHAR(MAX)
SELECT @result = '<rows>'
; WITH
cruftyData AS (
SELECT
e.empId
, a.attributeName
, (SELECT ea.attributeVal AS x FOR XML RAW) AS attributeValXml
FROM employee AS e
INNER JOIN employeeAttributes AS ea
ON ea.empId = e.empId
INNER JOIN attributes AS a
ON a.attributeId = ea.attributeId
)
, data AS (
SELECT
empId
, attributeName
, SUBSTRING(attributeValXml, 9, LEN(attributeValXml)-11) AS attributeVal
, ROW_NUMBER() OVER (PARTITION BY empId ORDER BY attributeName DESC) AS down
, ROW_NUMBER() OVER (PARTITION BY empId ORDER BY attributeName) AS up
FROM cruftyData
)
, xmlData AS (
SELECT
empId
, up
, CASE WHEN up <> 1 THEN '' ELSE '<row id="' + CAST (empId AS NVARCHAR) + '">' END AS xml1
, '<' + attributeName + '>' + attributeVal + '</' + attributeName + '>' AS xml2
, CASE WHEN down <> 1 THEN '' ELSE '</row>' END AS xml3
FROM data
)
SELECT @result = @result + xml1 + xml2 + xml3
FROM xmlData
ORDER BY empId, up
SELECT @result = @result + '</rows>'
SELECT @result
XML结尾于 @result 变量.您可以使用以下方法检查它是否为格式正确的XML:
The XML ends up in the @result variable. You can check that it is well-formed XML using:
SELECT CAST(@result AS XML)
最终的XML看起来像这样:
The final XML looks like this:
<rows><row id="1"><attribute1>someValue1</attribute1><attribute2>someValue2</attribute2></row><row id="2"><attribute1>someValue3</attribute1><attribute3>someValue4 & <>!</attribute3></row></rows>
这篇关于SQL Server从JOINed select语句生成XML数据行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
