Announcement

Collapse
No announcement yet.

Nested While loop in SQL Statement

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Nested While loop in SQL Statement



    Hi, I am attempting to build a filed from a nested while loop running on an XML file.

    The SQL is as follows:

    I can't see the right place to paste while loop as it relies on a count of an xml node for the number of loops to perform.

    Can anyone help please?

    Code:
    --database to be used--
     
    Use
    midata
     
    Go
     
    --add the xml file as a one line XML type data field into a table called tempxml--
     
    INSERT
    INTO tempXML(XMLData, LoadedDateTime)
     
    SELECT
    CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
     
    --select the xml file from this location---
     
    FROM
    OPENROWSET(BULK '\\Reports\TestXML\TESTXML.xml', SINGLE_BLOB) AS x;
     
     
    --make declarations of variables and data types--
     
    DECLARE
    @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX);
     
    --select the xml field in the temp table--
     
    SELECT
    @XML = xmldata FROM tempXML;
     
    --get bespoke namescaes info from supplier website--
     
    WITH
    XMLNAMESPACES('http://www.example.biz' as my)
     
    --check database for the existence of the record--
     
    --set the field transaction type as a variable, then search concatenation of the order number and ticket number in the database to see if the
     
    -- record exists. If it does exist then this record will be a refund so the only field to update is the "Transaction type to "refund/reissue" etc.
     
    UPDATE
    XML_TEST
     
    --specify the field(s) you would like to update being = to the xml field where the data is stored.
     
         
     
    SET transactionType = XC.value('../../@TransactionType', 'varchar(50)')
     
      
     
    FROM TempXML 
     
          
     
    --using xml.nodes specify a number of single column temp tables for each different node required--  
     
      
    cross apply
     
       XMLData
    .nodes('my:Handoff/ImmediateDetail') AS IDT(IDC)
     
      
    Cross Apply
     
       IDC
    .nodes('Segment[1]/Ticket') AS XT(XC)
     
       
    cross apply
     
       IDC
    .nodes('Segment[1]/Leg[1]') AS LEG1T(LEG1C)
     
        
    cross apply
     
       IDC
    .nodes('Segment/Leg/Origin') AS LORT(LORC)
     
      
    cross apply
     
       IDC
    .nodes('Segment/Leg/Destination') AS LDEST(LDESC)
     
      
    cross apply
     
       IDC
    .nodes('Segment/Leg') AS LEGT(LEGC)
     
      
     
    WHERE
     
       LEG1C
    .value('@Direction'  , 'varchar(50)' ) = 'Outbound'  AND
     
       orderRef
    +'-'+ticketRef = ISNULL(IDC.value('../@OrderRef', 'bigint'), IDC.value('@IssueRef', 'bigint')) +'-'+ XC.value('@Ref', 'varchar(50)' );
     
     
    IF
    @@ROWCOUNT=0
     
    WITH
    XMLNAMESPACES('http://www.example.biz' as my)
     
    INSERT
    INTO XML_TEST (TransactionType, OrderRef, TransDate, JourneyTime, Distance, TicketRef, Client, Account, contactEmail, Booker, travellerforename, travellersurname, Origin, Destination, Route, Class, TicketCode, TicketType, TrainOperator, TravelDate, Fare, LowFare, HighFare, fulfilmentType, travelReason, railCo2, airCo2, carPetrolCo2, carDieselCo2,motorcycleCo2, UDF1,UDF2,UDF3,UDF4,UDF5,UDF6,UDF11,UDF12,UDF13,UDF14,Full_Routing)
     
     
    SELECT
     
    XC
    .value('../../@TransactionType', 'varchar(50)') TransactionType,
     
    ISNULL(IDC.value('../@OrderRef', 'bigint'), IDC.value('@IssueRef', 'bigint'))  AS OrderRef,
     
    XC
    .value('../../@TransactionDate', 'datetime')TransDate,
     
    XC
    .value('../@JourneyTime', 'int')JourneyTime,
     
    XC
    .value('../@Distance', 'nvarchar(10)' )Distance,
     
    XC
    .value('@Ref', 'varchar(50)' )TicketRef,
     
    ADC
    .value('@Organisation', 'varchar(50)' )Client,
     
    ACC
    .value('@ExternalRef', 'varchar(50)' )Account,
     
    DCC
    .value('@Address[1]', 'varchar(50)' )contactEmail,
     
    IDC
    .value(N'(CustomField[@Code="ZNN"]/@Value)[1]',N'varchar(50)') AS Booker,
     
    PC
    .value('@FirstName','varchar(50)' )travellerforename,
     
    PC
    .value('@LastName','varchar(50)' )travellersurname,
     
    ORC
    .value('@Name','Varchar(50)' )Origin,
     
    DESTC
    .value('@Name','Varchar(50)' )Destination,
     
    XC
    .value('@Route', 'varchar(50)' )Route,
     
    XC
    .value('@Class' , 'varchar(50)' )Class,
     
    XC
    .value('@Code' , 'varchar(50)' )TicketCode,
     
    XC
    .value('@Name' , 'varchar(50)' )TicketType,
     
    TOPC
    .value('@Name' , 'varchar(50)' )TrainOperator,
     
    LEG1C
    .value('@Departure' , 'Datetime' )TravelDate,
     
    FAREC
    .value('@TotalAmount' , 'nvarchar(10)' )Fare,
     
    FAREXC
    .value('@OfferedFare' , 'nvarchar(10)' )LowFare,
     
    FAREXC
    .value('@NormalFare' , 'nvarchar(10)' )HighFare,
     
    IDC
    .value('../@DeliveryMethod' , 'nvarchar(10)' )fulfilmentType,
     
    FAREXC
    .value('@Reason' , 'varchar(50)' ) travelreason,
     
      
     
    CEC
    .value(N'(CarbonEmissions[@TransportType="Rail"]/@Emissions)[1]',N'decimal(10,4)') AS railCo2,
     
    CEC
    .value(N'(CarbonEmissions[@TransportType="Aeroplane"]/@Emissions)[1]',N'decimal(10,4)') AS airCo2,
     
    CEC
    .value(N'(CarbonEmissions[@TransportType="Car - Petrol"]/@Emissions)[1]',N'decimal(10,4)') AS CarPetrolCo2,
     
    CEC
    .value(N'(CarbonEmissions[@TransportType="Car - Diesel"]/@Emissions)[1]',N'decimal(10,4)') AS CarDieselCo2,
     
    CEC
    .value(N'(CarbonEmissions[@TransportType="Motorcycle"]/@Emissions)[1]',N'decimal(10,4)') AS MotorcycleCo2,
     
    PAC
    .value(N'(CustomField[@Code="1"]/@Value)[1]',N'nvarchar(250)') AS UDF1,
     
    PAC
    .value(N'(CustomField[@Code="2"]/@Value)[1]',N'nvarchar(250)') AS UDF2,
     
    PAC
    .value(N'(CustomField[@Code="3"]/@Value)[1]',N'nvarchar(250)') AS UDF3,
     
    PAC
    .value(N'(CustomField[@Code="4"]/@Value)[1]',N'nvarchar(250)') AS UDF4,
     
    PAC
    .value(N'(CustomField[@Code="5"]/@Value)[1]',N'nvarchar(250)') AS UDF5,
     
    PAC
    .value(N'(CustomField[@Code="6"]/@Value)[1]',N'nvarchar(250)') AS UDF6,
     
    IDC
    .value(N'(CustomField/@Value)[1]',N'nvarchar(250)') AS UDF11,
     
    IDC
    .value(N'(CustomField/@Value)[2]',N'nvarchar(250)') AS UDF12,
     
    IDC
    .value(N'(CustomField/@Value)[3]',N'nvarchar(250)') AS UDF13,
     
    IDC
    .value(N'(CustomField/@Value)[4]',N'nvarchar(250)') AS UDF14,
     
    @temproute 
    AS Full_Routing
     
      
     
    FROM TempXML 
     
       
     
       
     
    --using xml.nodes specify a number of single column temp tables for each different node required--   
     
     
      
    cross apply
     
       XMLData
    .nodes('my:Handoff/ImmediateDetail') AS IDT(IDC)
     
      
    Cross Apply
     
       IDC
    .nodes('Segment[1]/Ticket') AS XT(XC)
     
      
    Cross apply
     
       IDC
    .nodes('AccountContact/Address') AS ADT(ADC)
     
      
    Cross apply
     
       IDC
    .nodes('Account') AS ACT(ACC)
     
      
    Cross apply
     
       IDC
    .nodes('DeliveryContact/Email') AS DCT(DCC)
     
      
    cross apply
     
       IDC
    .nodes('PassengerGroup/Passenger/Person') AS PT(PC)
     
      
    cross apply
     
       IDC
    .nodes('Segment[1]/Origin') AS ORT(ORC)
     
      
    cross apply
     
       IDC
    .nodes('Segment[1]/Destination') AS DESTT(DESTC)
     
       
    cross apply
     
       IDC
    .nodes('Segment[1]/Leg[1]') AS LEG1T(LEG1C)
     
       
    cross apply
     
       IDC
    .nodes('Segment[1]/Leg[1]/TOC') AS TOPT(TOPC)
     
       
    cross apply
     
       IDC
    .nodes('Segment[1]/Ticket/Sale/Fare') AS FARET(FAREC)
     
       
    cross apply
     
       IDC
    .nodes('Segment[1]/Ticket/FareException') AS FAREXT(FAREXC)
     
      
    cross apply
     
       IDC
    .nodes('Segment[1]/CarbonEmissionDetails') AS CET(CEC)
     
      
    cross apply
     
       IDC
    .nodes('PassengerGroup') AS PGT(PGC)
     
      
    cross apply
     
       PGC
    .nodes('Passenger') AS PAT(PAC)
     
      
    cross apply
     
       IDC
    .nodes('Segment/Leg') AS LEGT(LEGC)
     
      
    cross apply
     
       IDC
    .nodes('Segment/Leg/Origin') AS LORT(LORC)
     
      
    cross apply
     
       IDC
    .nodes('Segment/Leg/Destination') AS LDEST(LDESC)
     
      
     
     
      
     
      
    --standard where clause--
     
    WHERE
     
       LEG1C
    .value('@Direction'  , 'varchar(50)' ) = 'Outbound'
     
      
     
    --delete temp table entry after upload/update sucessful--
     
      
     
    DELETE FROM
    tempXML
    The nested while loop is here:

    Code:
     
     
    DECLARE
     
    @cnt INT , @temprouteor varchar(50),@temproute varchar(50);
    
     
     
     
    SET
     
    @cnt = 1
     
    
     
     
    WHILE @cnt < COUNT(LEGC.nodes('Origin'))-1 
     
    
     
     
    BEGIN
    
     
     
     
    SET @temprouteor = @temprouteor + LORC.value('@CRSCode' , 'Varchar(50)' ) + '/'
    
     
     
     
    SET @cnt = @cnt +1
     
    
     
     
    WHILE @cnt = COUNT(LEGC.nodes('Origin'))
     
    
     
     
    BEGIN
    
     
     
     
    SET @temproute = @temprouteor + LDESC.value('@CRSCode' , 'Varchar(50)' );
    
     
     
     
    END
    
     
     
     
    END;

    Cheers

    Dave
Working...
X