Geek Answers Handbook

SQL Server 2012: select xml with reusable and ungrouped itemset

For 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 create 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 get:

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 tried a couple of other queries, but to no avail.

Please note: the XML structure cannot be modified.

+4
source share
2 answers

Your XML seems to depend on ordered pairs (the first partner comes with the first date, the second partner comes with the second date, etc. etc.). Not only that, but both columns are listed inside the same parent node. So you will need to do something like this. Fortunately for you, xml is order sensitive.

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
source

, , 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
+1

More articles:


All Articles