SQL Server从JOINed select语句生成XML数据行

I have a three tables in SQL Server 2008 which are setup as follows:

我在SQL Server 2008中有三个表,其设置如下:

EMPLOYEE TABLE

empid(PK)
1
2

joined to EMPLOYEEATTRIBUTES

加入了EMPLOYEEATTRIBUTES

dataId(PK) | empId(FK) | attributeid | attributeVal
10 | 1 | A1 | somevalue1
20 | 1 | A2 | somevalue2
30 | 2 | A1 | somevalue3
40 | 2 | A3 | somevalue4

joined to ATTRIBUTES

加入了ATTRIBUTES

attributeid | attributeName
A1 | attribute1
A2 | attribute2
A3 | attribute3

I need to get the xml data out into the following format

我需要将xml数据转换为以下格式

<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??

任何人都知道如何做到这一点?

3 个解决方案

#1

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查询。

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 FOR XML选项无法生成所需输出中规定的动态元素名称。因此,我的第一个答案是考虑简单地返回传统的SQL结果集并让客户端生成XML。这是一个非常简单的流式转换。但是,这可能不是您的选择,因此我们继续让SQL Server生成XML。

My second thought was to use SQL Server's built-in XQuery functionality to perform the transformation, thus:

我的第二个想法是使用SQL Server的内置XQuery功能来执行转换,因此:

/* 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>
    ')

Alas, this does not work. SQL Server complains:

唉,这不起作用。 SQL Server抱怨:

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.

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...

显然,XQuery实现受到与FOR XML功能相同的限制。所以,我的第二个答案是建议在客户端生成XML :)但是如果你坚持从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 :)

总体策略是放弃SQL Server的本地SQL设施。相反,我们将使用字符串连接来构建XML文档。如果这种做法令人反感,你现在可以停止阅读:)

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'

Note that I have changed the value of the last attribute in the provided example to include some XML-unfriendly characters.

请注意,我已经更改了提供的示例中的最后一个属性的值,以包含一些XML不友好的字符。

Now, put together a basic SQL query to perform the necessary joins:

现在,将基本的SQL查询放在一起以执行必要的连接:

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

which gives this result:

这给出了这个结果:

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

with results:

empId   attributeName   attributeValXml
1       attribute1      someValue1
1       attribute2      someValue2
2       attribute1      someValue3
2       attribute3      someValue4 & <>!

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 :)

这可确保现在可以在XML文档中安全地使用属性值。属性名称怎么样? XML属性名称的规则比元素内容的规则更严格。我们假设属性名称是有效的XML标识符。如果不是这样,则需要设计一些方案将数据库中的名称转换为有效的XML名称。这是留给读者的练习:)

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

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

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.

我们现在可以识别员工的第一个属性,因为up将是1.可以使用向下列以类似的方式识别最后一个属性。

Armed with all of this, we are now equipped to perform the nasty business of building up the XML result using string concatenation.

有了这一切,我们现在有能力执行使用字符串连接构建XML结果的讨厌业务。

; 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

with the result:

结果:

xml1          xml2                                                 xml3
<row id="1">  <attribute1>someValue1</attribute1>        
              <attribute2>someValue2</attribute2>                  </row>
<row id="2">  <attribute1>someValue3</attribute1>        
              <attribute3>someValue4 &amp; &lt;&gt;!</attribute3>  </row>

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:

剩下的就是将所有行连接在一起,并添加根行标记。由于T-SQL(尚未)具有字符串连接聚合函数,因此我们将使用变量作为累加器。这是最后的查询,其所有的hacky荣耀:

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

The XML ends up in the @result variable. You can check that it is well-formed XML using:

XML最终出现在@result变量中。您可以使用以下命令检查它是否是格式良好的XML:

SELECT CAST(@result AS XML)

The final XML looks like this:

最终的XML看起来像这样:

<rows><row id="1"><attribute1>someValue1</attribute1><attribute2>someValue2</attribute2></row><row id="2"><attribute1>someValue3</attribute1><attribute3>someValue4 &amp; &lt;&gt;!</attribute3></row></rows>

#2

You can get close - but you can't get your desired output 100%.

你可以近距离接触 - 但你不能100%得到你想要的输出。

Using this query:

使用此查询:

SELECT
    EmpID AS '@empid',
    (
        SELECT 
           a.AttributeName AS '@name',
           ea.AttributeVal
        FROM dbo.EmployeeAttributes ea 
        INNER JOIN dbo.Attributes a ON ea.AttributeId = a.AttributeId
        WHERE ea.EmpID = e.EmpID
        FOR XML PATH ('attribute'), TYPE
    )
FROM dbo.Employee e
FOR XML PATH('row'), ROOT('rows')

you get this output:

你得到这个输出:

<rows>
  <row empid="1">
    <attribute name="Attribute1">
      <AttributeVal>SomeValue1</AttributeVal>
    </attribute>
    <attribute name="attribute2">
      <AttributeVal>SomeValue2</AttributeVal>
    </attribute>
  </row>
  <row empid="2">
    <attribute name="Attribute1">
      <AttributeVal>SomeValue3</AttributeVal>
    </attribute>
    <attribute name="attribute3">
      <AttributeVal>SomeValue4</AttributeVal>
    </attribute>
  </row>
</rows>

What you cannot do is make the inner XML nodes have tag names that match the attribute name - you have to use some fixed tag name (like <attribute> in my sample), and then apply the values that are retrieved from your tables as either attributes on those XML tags (like the name= attribute in my sample) or as XML element values.

您不能做的是使内部XML节点具有与属性名称匹配的标记名称 - 您必须使用一些固定的标记名称(如我的示例中的 ),然后将从表中检索到的值应用为这些XML标记上的属性(如我的示例中的name =属性)或XML元素值。

As far as I know, there is no way to use the AttributeValue as the XML tag name....

据我所知,没有办法使用AttributeValue作为XML标签名称....

#3

Here's an answer, but the PIVOT command limits you in that you have to know the name of your attributes in advance. With a little tweaking, you could probably do this dynamically (try searching for dynamic pivot in SQL Server 2005):

这是一个答案,但PIVOT命令限制了您必须提前知道属性的名称。稍微调整一下,您可以动态地执行此操作(尝试在SQL Server 2005中搜索动态数据透视):

DECLARE @Employee TABLE ( empid INT )
DECLARE @EA TABLE
    (
      dataid INT
    , empid INT
    , attributeid CHAR(2)
    , AttributeVal VARCHAR(100)
    )
DECLARE @Attributes TABLE
    (
      AttributeID CHAR(2)
    , AttributeName VARCHAR(100)
    )

INSERT  INTO @Employee
VALUES  ( 1 ),
        ( 2 )

INSERT  INTO @EA
        ( dataid, empid, attributeid, AttributeVal )
VALUES  ( 10, 1, 'A1', 'somevalue1' )
    , ( 20, 1, 'A2', 'somevalue2' )
    , ( 30, 2, 'A1', 'somevalue3' )
    , ( 40, 2, 'A3', 'somevalue4' )

INSERT  INTO @Attributes
        ( AttributeID, AttributeName )
VALUES  ( 'A1', 'attribute1' )
        ,
        ( 'A2', 'attribute2' )
        ,
        ( 'A3', 'attribute3' )

SELECT  empID as '@empid'
      , attribute1
      , attribute2
      , attribute3
      , attribute4
FROM    ( SELECT    e.empid
                  , a.AttributeName
                  , ea.AttributeVal
          FROM      @Employee e
                    JOIN @EA ea ON e.empid = ea.empid
                    JOIN @Attributes a ON ea.attributeid = a.attributeid
        ) ps PIVOT
( MIN(AttributeVal) FOR AttributeName IN ( [attribute1], [attribute2], [attribute3], [attribute4] ) ) AS pvt    
FOR XML PATH('row'), ROOT('rows')