SQL Server 2012: Select xml with repeated and ungrouped set of elements



For the XML below:



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


No comments:

Post a Comment