Pour le XML ci-dessous:
<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>
Je voudrais générer le tableau suivant:
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
Maintenant, je reçois:
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
En utilisant cette requête:
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 DVnodes('./RaitingDate') as Q(S) cross apply DVnodes('./MinimumRatingPartner') as M(R) order by MRP, RatingDate
J'ai essayé quelques autres requêtes, mais sans succès.
Veuillez noter: la structure XML ne peut pas être modifiée.
Votre XML semble dépendre de paires ordonnées (le premier partenaire va avec la première date, le deuxième partenaire va avec la deuxième date, etc etc). Non seulement cela, mais les deux colonnes sont répertoriées dans le même nœud parent. Donc vous devrez faire quelque chose comme ça. Heureusement pour vous xml est sensible à la command.
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 DVvalue('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 DVvalue('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
Probablement pas la meilleure solution, mais fonctionne pour le xml donné
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