SQL Server从联接的SELECT语句

Question

产生XML数据行我已经在SQL Server 2008中的3个表内的设置如下：

EMPLOYEE表

empid(PK) 
1 
2 

加入到EMPLOYEEATTRIBUTES

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

加入ATTRIBUTES

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

我需要出去获得XML数据转换成以下格式

<rows> 
    <row empid="1"> 
    <attribute1>somevalue1</attribute1> 
    <attribute2>somevalue2</attribute1> 
    </row> 
    <row empid="2"> 
    <attribute1>somevalue3</attribute1> 
    <attribute3>somevalue4</attribute1> 
    </row> 
</rows> 

有谁知道如何可以做到这一点？

Answer 1

你可以关闭 - 但你不能得到你想要的输出100％。

使用此查询：

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

你会得到这样的输出：

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

什么你不能做的是使内部的XML节点具有匹配属性名称标签名称 - 你必须使用一些固定标签名称（如我的示例中的<attribute>），然后将从表中检索到的值作为这些XML标签上的属性（例如我的示例中的name=属性）或作为XML元素值。

据我所知，目前还没有办法使用AttributeValue作为XML标签名称....

Answer 2

这里有一个答案，但转动命令限制你，你必须知道的名字您的属性提前。随着一点点的调整，你也许可以做到这一点动态（尝试在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') 

Answer 3

如果你想跳过所有的血淋淋的细节，只是看到一个答案，查看SQL查询在这篇文章的底部。

这里的主要挑战是各种SQL Server选项无法生成所需输出中规定的动态元素名称。因此，我的第一个答案是考虑简单地返回一个常规的SQL结果集并让客户端生成XML。这是一个非常简单的流媒体转换。但是，这可能不适合您，所以我们继续让SQL Server生成XML。

我的第二个想法是使用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> 
    ') 

唉，这是行不通的。 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. 

显然，XQuery实现从相同的限制受到的FOR XML功能。所以，我的第二个回答是建议在客户端生成XML :)但是，如果你坚持从SQL生成XML，然后系好安全带...

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

让我们先从产生的样本数据集一起玩：在所提供的例子

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' 

请注意，我已经改变了最后一个属性的值包括一些XML不友好的字符。

现在，把一个基本的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 

这给出了这样的结果：

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

在最后一个属性的那些奇怪的字符，将会给我们带来麻烦。让我们改变查询来转义它们。

; 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名称。这是作为练习留给读者:)

下一个挑战是确保属性为每个员工分组在一起，并且我们可以知道我们何时处于组中的第一个或最后一个值。以下是更新的查询：

; 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 

唯一的变化是将下和了列添加到结果集：

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 

我们现在能够确定的第一个属性为雇员因为以上将是。最后的属性可以使用下的列以类似的方式识别。

有了这一切，我们现在可以执行使用字符串连接来构建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 

与结果：

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> 

剩下的是连接所有的行在一起，并添加根行标签。由于T-SQL没有（还）有字符串连接集合函数，因此我们将使用一个变量作为累加器。这里是最终的查询，在其所有的荣耀哈克：

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：

SELECT CAST(@result AS XML) 

最终的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>