sql-server - SQL 服务器 2012 : Select xml with repeated and ungrouped set of elements

Question

对于下面的 XML:

<Document>
    <ID>01</ID>
    <RaitingDate>2006-05-04T18:13:51.0Z</RaitingDate>
    <MinimumRatingPartner>MinimumRatingPartner1</MinimumRatingPartner>
    <RaitingDate>2006-05-04T18:13:52.0Z</RaitingDate>
    <MinimumRatingPartner>MinimumRatingPartner2</MinimumRatingPartner>
    <RaitingDate>2006-05-04T18:13:53.0Z</RaitingDate>
    <MinimumRatingPartner>MinimumRatingPartner3</MinimumRatingPartner>
</Document>

我想生成下表:

RatingDate                  MRP
-----------------------    ---------------------
2006-05-04 18:13:51.000    MinimumRatingPartner1
2006-05-04 18:13:52.000    MinimumRatingPartner2
2006-05-04 18:13:53.000    MinimumRatingPartner3

现在我得到:

RatingDate                  MRP
-----------------------    ---------------------
2006-05-04 18:13:51.000    MinimumRatingPartner1
2006-05-04 18:13:52.000    MinimumRatingPartner1
2006-05-04 18:13:53.000    MinimumRatingPartner1
2006-05-04 18:13:51.000    MinimumRatingPartner2
2006-05-04 18:13:52.000    MinimumRatingPartner2
2006-05-04 18:13:53.000    MinimumRatingPartner2
2006-05-04 18:13:51.000    MinimumRatingPartner3
2006-05-04 18:13:52.000    MinimumRatingPartner3
2006-05-04 18:13:53.000    MinimumRatingPartner3

使用这个查询:

DECLARE @XML XML = 
'<Document>
    <ID>01</ID>
    <RaitingDate>2006-05-04T18:13:51.0Z</RaitingDate>
    <MinimumRatingPartner>MinimumRatingPartner1</MinimumRatingPartner>
    <RaitingDate>2006-05-04T18:13:52.0Z</RaitingDate>
    <MinimumRatingPartner>MinimumRatingPartner2</MinimumRatingPartner>
    <RaitingDate>2006-05-04T18:13:53.0Z</RaitingDate>
    <MinimumRatingPartner>MinimumRatingPartner3</MinimumRatingPartner>
</Document>'

SELECT
    RatingDate = s.value('text()[1]', 'datetime')
    ,MRP =r.value('text()[1]', 'nvarchar(50)')
FROM
    @XML.nodes('Document') as D(V)
cross apply
    D.V.nodes('./RaitingDate') as Q(S)
cross apply
    D.V.nodes('./MinimumRatingPartner') as M(R)
order by MRP, RatingDate

我尝试了其他几个查询，但没有成功。

请注意:XML 结构无法更改。

Answer 1

您的 XML 似乎取决于有序对(第一个伙伴与第一个约会，第二个伙伴与第二个约会等等)。不仅如此，两列都列在同一个父节点中。所以你必须做这样的事情。幸运的是，xml 对顺序敏感。

DECLARE @XML XML = 
'<Document>
    <ID>01</ID>
    <RaitingDate>2006-05-04T18:13:51.0Z</RaitingDate>
    <MinimumRatingPartner>MinimumRatingPartner1</MinimumRatingPartner>
    <RaitingDate>2006-05-04T18:13:52.0Z</RaitingDate>
    <MinimumRatingPartner>MinimumRatingPartner2</MinimumRatingPartner>
    <RaitingDate>2006-05-04T18:13:53.0Z</RaitingDate>
    <MinimumRatingPartner>MinimumRatingPartner3</MinimumRatingPartner>
</Document>'

SELECT MinimumRatingPartner, RatingDate FROM
(SELECT
    D.V.value('text()[1]', 'datetime') AS RatingDate,
    ROW_NUMBER() over (order by @@rowcount) AS RowNum
FROM @XML.nodes('Document/RaitingDate') as D(V)) Dates
INNER JOIN
(SELECT
    D.V.value('text()[1]', 'nvarchar(50)') AS MinimumRatingPartner,
    ROW_NUMBER() over (order by @@rowcount) AS RowNum
FROM @XML.nodes('Document/MinimumRatingPartner') as D(V)) Partners
ON Dates.RowNum = Partners.RowNum