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?

VB:
--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:

VB:
 
 
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