通过 OPENQUERY 和 OPENROWSET 获取的结果集中不允许有重复的列名

Question

如何在 MSSQL 2005 中使用 OPENXML 获取具有相同列名的第二列？

这是我计划获得的结果集。

列数据 1 列数据 2

A B

C D

E F

DECLARE @hDoc int, @xmldata varchar(max)
SELECT @xmldata = 

'<?xml version="1.0" encoding="utf-8" ?>
<reportResponse>
<reportDataRow rowNum="1">
<columnData colNum="1">
  <data>A</data>
</columnData>
<columnData colNum="2">
  <data>B</data>
</columnData>
</reportDataRow>
<reportDataRow rowNum="2">
<columnData colNum="1">
  <data>C</data>
</columnData>
<columnData colNum="2">
  <data>D</data>
</columnData>
</reportDataRow>
<reportDataRow rowNum="3">
<columnData colNum="1">
  <data>E</data>
</columnData>
<columnData colNum="2">
  <data>F</data>
</columnData>
</reportDataRow>
</reportResponse>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT *
FROM OPENXML(@hDoc, '/reportResponse/reportDataRow',2)
WITH (columnData varchar(50) , columnData2 varchar(50) )


SELECT *
FROM OPENXML(@hDoc, '/reportResponse/reportDataRow/columnData',2)
WITH (data varchar(50))

EXEC sp_xml_removedocument @hDoc

Answer 1

我认为如果不使用 OPENXML 作为临时行集，这是不可能的：

DECLARE @hDoc INT, @xmldata VARCHAR(MAX)
SELECT @xmldata = 

'<?xml version="1.0" encoding="utf-8" ?>
<reportResponse>
<reportDataRow rowNum="1">
<columnData colNum="1">
  <data>A</data>
</columnData>
<columnData colNum="2">
  <data>B</data>
</columnData>
</reportDataRow>
<reportDataRow rowNum="2">
<columnData colNum="1">
  <data>C</data>
</columnData>
<columnData colNum="2">
  <data>D</data>
</columnData>
</reportDataRow>
<reportDataRow rowNum="3">
<columnData colNum="1">
  <data>E</data>
</columnData>
<columnData colNum="2">
  <data>F</data>
</columnData>
</reportDataRow>
</reportResponse>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

;WITH xmlCTE
AS
(
        SELECT *
        FROM OPENXML(@hDoc, '/reportResponse/reportDataRow/columnData',2)
        WITH (DATA VARCHAR(50) 
             ,rowNum INT       '../@rowNum'
             ,colNum INT       '@colNum')
)
SELECT c1.DATA AS columnData1
       ,c2.DATA AS columnData2
FROM xmlCTE AS c1
JOIN xmlCTE AS c2
ON   c1.rowNum = c2.rowNum
AND  c2.colNum = 2
WHERE c1.colNum = 1

EXEC sp_xml_removedocument @hDoc

Answer 2

谢谢。我通过使用 PIVOT 找到了答案。在我的实际情况中，我有超过 10 列和数千行，因此自连接并不容易。顺便说一句，这是为了解析来自 PayPal reportEngineResponse 的 xml 数据集。下面是分享的答案。

DECLARE @hDoc INT, @xmldata VARCHAR(MAX)
SELECT @xmldata = 

'<?xml version="1.0" encoding="utf-8" ?>
<reportResponse>
<reportDataRow rowNum="1">
<columnData colNum="1">
  <data>A</data>
</columnData>
<columnData colNum="2">
  <data>B</data>
</columnData>
</reportDataRow>
<reportDataRow rowNum="2">
<columnData colNum="1">
<data>C</data>
</columnData>
<columnData colNum="2">
<data>D</data>
</columnData>
</reportDataRow>
<reportDataRow rowNum="3">
<columnData colNum="1">
<data>E</data>
</columnData>
<columnData colNum="2">
<data>F</data>
</columnData>
</reportDataRow>
</reportResponse>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT [1] as row1, [2] as row2
FROM
(SELECT *
FROM OPENXML(@hDoc, '/reportResponse/reportDataRow/columnData',2)
WITH (data VARCHAR(50) 
 ,rowNum INT       '../@rowNum'
 ,colNum INT       '@colNum')) P
PIVOT
(
MAX(data)
FOR colNum IN ([1],[2]) 
)AS pvt

EXEC sp_xml_removedocument @hDoc