While loop in sql server 2008 that refers to xml.nodes

Question

I have the following issue:

I have these 2 while loops that iterates through a node of an xml file but I can't see where to paste the while loop as it needs to be able to reference the xml nodes stated.

DECLARE @cnt INT = 1, @temprouteor varchar(50),@temproute varchar(50);
SET @cnt = 1



WHILE @cnt < COUNT(IDC.nodes('Segment/Leg'))-1 

BEGIN
SET @temprouteor = @temprouteor + LORC.value('@CRSCode' , 'Varchar(50)' ) + '/'
SET @cnt = @cnt +1
END

WHILE @cnt = COUNT(IDC.nodes('Segment/Leg')) 
BEGIN
SET @temproute = @temprouteor + LDESC.value('@CRSCode' , 'Varchar(50)' );
END

The 3 nodes are:

LEGC
LORC
LDESC

Does anyone have any ideas on how I can achieve this?

 <Leg Ref="101" Direction="Outbound" Departure="2016-12-22T06:51:00"       Arrival="2016-12-22T07:04:00" TransportMode="TRAIN">
 <Origin UICCode="7034740" NLCCode="3474" CRSCode="TVP" Name="TIVERTON   PARKWAY" /> 
 <Destination UICCode="7034710" NLCCode="3471" CRSCode="TAU" Name="TAUNTON" /> 
 <TOC Code="GW" Name="Great Western Railway" /> 
 <Reservation PassengerRef="77814233" AccomodationUnit="B33" /> 
 <TrainRoute Ref="101" OriginDeparture="2016-12-22T05:30:00" DestinationArrival="2016-12-22T09:21:00">
 <Origin UICCode="7035800" NLCCode="3580" CRSCode="PLY" Name="PLYMOUTH" /> 
 <Destination UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" /> 
 </TrainRoute>
 </Leg>
 <Leg Ref="102" Direction="Outbound" Departure="2016-12-22T07:18:00" Arrival="2016-12-22T09:00:00" TransportMode="TRAIN">
 <Origin UICCode="7034710" NLCCode="3471" CRSCode="TAU" Name="TAUNTON" /> 
 <Destination UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" /> 
 <TOC Code="GW" Name="Great Western Railway" /> 
 <Reservation PassengerRef="77814233" AccomodationUnit="D64" /> 
 <TrainRoute Ref="102" OriginDeparture="2016-12-22T05:53:00" DestinationArrival="2016-12-22T09:00:00">
 <Origin UICCode="7035800" NLCCode="3580" CRSCode="PLY" Name="PLYMOUTH" /> 
 <Destination UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" /> 
 </TrainRoute>
 </Leg>
 <Leg Ref="103" Direction="Outbound" Departure="2016-12-22T09:15:00" Arrival="2016-12-22T09:21:00" TransportMode="TRAIN">
 <Origin UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" /> 
 <Destination UICCode="7030000" NLCCode="3000" CRSCode="AML" Name="ACTON MAIN LINE" /> 
 <TOC Code="GW" Name="Great Western Railway" /> 
 <TrainRoute Ref="103" OriginDeparture="2016-12-22T09:15:00" DestinationArrival="2016-12-22T09:40:00">
 <Origin UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" /> 
 <Destination UICCode="7031360" NLCCode="3136" CRSCode="GFD" Name="GREENFORD" /> 
 </TrainRoute>
 </Leg>
 <Leg Ref="601" Direction="Return" Departure="2016-12-22T14:33:00" Arrival="2016-12-22T14:42:00" TransportMode="TRAIN">
 <Origin UICCode="7030000" NLCCode="3000" CRSCode="AML" Name="ACTON MAIN LINE" /> 
 <Destination UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" /> 
 <TOC Code="GW" Name="Great Western Railway" /> 
 <TrainRoute Ref="601" OriginDeparture="2016-12-22T14:16:00" DestinationArrival="2016-12-22T14:42:00">
 <Origin UICCode="7031360" NLCCode="3136" CRSCode="GFD" Name="GREENFORD" /> 
 <Destination UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" /> 
 </TrainRoute>
 </Leg>
 <Leg Ref="602" Direction="Return" Departure="2016-12-22T15:06:00" Arrival="2016-12-22T17:18:00" TransportMode="TRAIN">
 <Origin UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" /> 
 <Destination UICCode="7034740" NLCCode="3474" CRSCode="TVP" Name="TIVERTON PARKWAY" /> 
 <TOC Code="GW" Name="Great Western Railway" /> 
 <Reservation PassengerRef="77814233" AccomodationUnit="B84" /> 
 <TrainRoute Ref="602" OriginDeparture="2016-12-22T15:06:00" DestinationArrival="2016-12-22T20:42:00">
 <Origin UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" /> 
 <Destination UICCode="7035260" NLCCode="3526" CRSCode="PNZ" Name="PENZANCE" /> 
 </TrainRoute>
 </Leg>

Expected output is to see a concatenated routing of all the legs of the train journey.

In this case: TVP/TAU/PAD/AML/PAD/TVP

This is - for sure! - an absolutely wrong approach... Please poste a (reduced!) example of you XLM and the needed output. — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 15, 2017 at 13:54
posted above for you. Thanks. I am learning as I go and this has me realty stumped. — Dave Edmonds
– Dave Edmonds, Commented Feb 15, 2017 at 14:00
Your sample xml does not contain all the information in your desired output. The desired output should be the output from the sample data provided. — Sean Lange
– Sean Lange, Commented Feb 15, 2017 at 14:08
It is posted when I edit the post but only shows the first bit when I save it. Not sure how else to send it to you. — Dave Edmonds
– Dave Edmonds, Commented Feb 15, 2017 at 14:08

Gottfried Lesigang · Accepted Answer · 2017-02-15 17:24:59Z

2

Attention

WHILE @cnt < COUNT(IDC.nodes('Segment/Leg'))-1

Shows clearly, that there is deeper nesting, at least one more element around your <Leg>elements called <Segment>. I do not know your full XML. You've either to reflect this in your XPath or you use .nodes(//Leg) for a deep search (not recommended).

I reduced this to two <Leg> nodes. The same applies with more of them:

DECLARE @xml XML=
N'<Leg Ref="101" Direction="Outbound" Departure="2016-12-22T06:51:00" Arrival="2016-12-22T07:04:00" TransportMode="TRAIN">
  <Origin UICCode="7034740" NLCCode="3474" CRSCode="TVP" Name="TIVERTON   PARKWAY" />
  <Destination UICCode="7034710" NLCCode="3471" CRSCode="TAU" Name="TAUNTON" />
  <TOC Code="GW" Name="Great Western Railway" />
  <Reservation PassengerRef="77814233" AccomodationUnit="B33" />
  <TrainRoute Ref="101" OriginDeparture="2016-12-22T05:30:00" DestinationArrival="2016-12-22T09:21:00">
    <Origin UICCode="7035800" NLCCode="3580" CRSCode="PLY" Name="PLYMOUTH" />
    <Destination UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" />
  </TrainRoute>
</Leg>
<Leg Ref="102" Direction="Outbound" Departure="2016-12-22T07:18:00" Arrival="2016-12-22T09:00:00" TransportMode="TRAIN">
  <Origin UICCode="7034710" NLCCode="3471" CRSCode="TAU" Name="TAUNTON" />
  <Destination UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" />
  <TOC Code="GW" Name="Great Western Railway" />
  <Reservation PassengerRef="77814233" AccomodationUnit="D64" />
  <TrainRoute Ref="102" OriginDeparture="2016-12-22T05:53:00" DestinationArrival="2016-12-22T09:00:00">
    <Origin UICCode="7035800" NLCCode="3580" CRSCode="PLY" Name="PLYMOUTH" />
    <Destination UICCode="7030870" NLCCode="3087" CRSCode="PAD" Name="LONDON PADDINGTON" />
  </TrainRoute>
</Leg>';

--This is the query

 SELECT l.value('@Ref','int') AS Leg_Ref
       ,l.value('@Direction','nvarchar(max)') AS Leg_Direction
       ,l.value('@Departure','datetime') AS Leg_Departure
       --more attributes in <Leg>
       ,l.value('(Origin/@UICCode)[1]','int') AS Origin_UICCode
       --more attributes in `<Leg><Origin>`
       --Same approach for following elements
       ,l.value('(TrainRoute/Origin/@UICCode)[1]','int') AS TrainRoute_Origin
       --and so on...
 FROM @xml.nodes('/Leg') AS A(l)

The result for the first two elements:

101 Outbound    2016-12-22 06:51:00.000 7034740 7035800
102 Outbound    2016-12-22 07:18:00.000 7034710 7035800

UPDATE Found your later added expected output

TVP/TAU/PAD/AML/PAD/TVP

This will fetch the nodes in question:

 SELECT l.value('@Ref','int') AS Leg_Ref
       ,l.value('(Origin/@CRSCode)[1]','nvarchar(max)') AS Origin_CRSCode
       ,l.value('(Destination/@CRSCode)[1]','nvarchar(max)') AS Destination_CRSCode
 FROM @xml.nodes('/Leg') AS A(l)

returns for the sample data

 Leg_Ref    Origin_CRSCode  Destination_CRSCode
101         TVP             TAU
102         TAU             PAD
103         PAD             AML
601         AML             PAD
602         PAD             TVP

But I do not know, how these nodes are connected. You'd probably need a recursive CTE.

UPDATE 2

From your comments I think you want this:

WITH AllLegs AS
(
     SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS PosNr
           ,l.value('@Ref','int') AS Leg_Ref
           ,l.value('(Origin/@CRSCode)[1]','nvarchar(max)') AS Orig
           ,l.value('(Destination/@CRSCode)[1]','nvarchar(max)') AS Dest
     FROM @xml.nodes('/Leg') AS A(l)
)
SELECT Orig + '/' + Dest
    + (
        SELECT '/' + Dest FROM AllLegs WHERE PosNr>1 ORDER BY PosNr FOR XML PATH('')
      )
 FROM AllLegs WHERE PosNr=1

The result

 TVP/TAU/PAD/AML/PAD/TVP

edited Feb 15, 2017 at 17:24

answered Feb 15, 2017 at 14:24

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Dave Edmonds Over a year ago

Hi, this is almost what I am trying to do. So after this I need to take the origin for each leg and separate if with a / and then for the last leg the destination only as per my example output. TVP/TAU/PAD/AML/PAD/TVP

Dave Edmonds Over a year ago

also, I do not know how many legs there will be in a journey so that's why I need to count them

Dave Edmonds Over a year ago

Just to clarify, there should only be 1 result showing the entire journey. I am so lost - thanks for your help so far.

Dave Edmonds Over a year ago

ok so this is what I have LEGC.value('(Origin/@CRSCode)[1]','nvarchar(max)') + '/' + LEGC.value('(Destination/@CRSCode)[1]','nvarchar(max)') but this only give me the first leg - is there a way to loop through all the legs?

Dave Edmonds Over a year ago

Ok got that thanks. I can make it work in the test data but I am having issues placing it into my main code.

|

Collectives™ on Stack Overflow

While loop in sql server 2008 that refers to xml.nodes

1 Answer 1

UPDATE Found your later added expected output

UPDATE 2

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE Found your later added expected output

UPDATE 2

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related