SQL Server 2012：选择具有重复和未组合元素集的xml

For the XML below:

对于以下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>

I would like to generate the following table:

我想生成下表:

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

Now I am getting:

现在我得到:

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

Using this query:

使用此查询:

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

I have tried couple other queries, but without success.

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

Please note: XML structure cannot be changed.

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

2 个解决方案

#1

Your XML appears to depend on ordered pairs (first partner goes with first date, second partner goes with second date etc etc). Not only that, but both columns are listed within the same parent node. So you'll have to do something like this. Lucky for you xml is order sensitive.

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

#2

Probably not the best solution, but works for the given xml

可能不是最好的解决方案,但适用于给定的xml

SELECT RaitingDate,MinimumRatingPartner 
FROM 
(
   SELECT ROW_NUMBER() OVER(ORDER BY RaitingDate) AS Num
         ,RaitingDate 
   FROM
        (
            SELECT
            Node.Data.value('(.)[1]','DATETIME') as RaitingDate
            FROM @XML.nodes('/Document/RaitingDate') Node(Data) 
        ) AS A
) AS DateTable

JOIN 

(
    SELECT ROW_NUMBER() OVER(ORDER BY MinimumRatingPartner) AS Num
         ,MinimumRatingPartner
    FROM
        (
            SELECT
            Node.Data.value('(.)[1]','VARCHAR(50)') as MinimumRatingPartner
            FROM @XML.nodes('/Document/MinimumRatingPartner') Node(Data) 
        ) AS B
) AS PartnerTable
ON DateTable.Num=PartnerTable.Num