View Single Post
  #1 (permalink)  
Old August 4th, 2008, 03:04 AM
Neal Neal is offline
Friend of Wrox
Points: 718, Level: 10
Points: 718, Level: 10 Points: 718, Level: 10 Points: 718, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: Ennis, Clare, Ireland.
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default SQL XML Explicit (Output file required)

Hi Guys,

SQL 2005

I have written the following SQL query. It executes ok in the results pane, but I need to write it to a '.xml' file (in the following location/format E:\XMLOutput_YYYYMMDD_HH:MM:SS.xml).

where YYYYMMDD_HH:MM:SS = Current date/time

Code:
SELECT 1 as Tag, NULL as Parent,
                E.Location as [Envelope!1!xmlns],
                NULL as [Header!2!MessageId!xml],
                NULL as [Header!2!SourceEndPointUser!xml],
                NULL as [Header!2!SourceEndPoint!xml],
                NULL as [Header!2!DestinationEndPoint!xml],
                NULL as [Header!2!Action!xml],            
                NULL as [Body!3!WeightbridgeImport!xml],        
                NULL as [WeightbridgeImport!4!Id!hide],
                NULL as [WeightbridgeImport!4!xmlns],
                NULL as [WeightbridgeImport!4!DocPurpose!xml],
                NULL as [WeightbridgeImport!4!SenderId!xml],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportTable!xml],
                NULL as [VCTWeightbridgeImportTable!5!Class],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportRef!xml],
                NULL as [VCTWeightbridgeImportLine!6!Class],
                NULL as [VCTWeightbridgeImportLine!6!VCTComments!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTInventLocationId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTItemId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTMovementId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalTime!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleDespatchDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleReg!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTWeightbridgeImportRef!xml]
FROM dbo.ABN_File_Header H JOIN dbo.Envelope E ON
H.Envelope_Id = E.Id
WHERE
H.DateTime_Transmit IS NULL 

UNION 

SELECT 2 as Tag, 1 as Parent, 
                NULL as [Envelope!1!xmlns],
                H.MessageId as [Header!2!MessageId!xml], 
                H.SourceEndPointUser as [Header!2!SourceEndPointUser!xml],
                H.SourceEndPoint as [Header!2!SourceEndPoint!xml],
                H.DestinationEndPoint as [Header!2!DestinationEndPoint!xml],
                H.[Action] as [Header!2!Action!xml],
                NULL as [Body!3!WeightbridgeImport!xml],        
                NULL as [WeightbridgeImport!4!Id!hide],
                NULL as [WeightbridgeImport!4!xmlns],
                NULL as [WeightbridgeImport!4!DocPurpose!xml],
                NULL as [WeightbridgeImport!4!SenderId!xml],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportTable!xml],
                NULL as [VCTWeightbridgeImportTable!5!Class],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportRef!xml],
                NULL as [VCTWeightbridgeImportLine!6!Class],
                NULL as [VCTWeightbridgeImportLine!6!VCTComments!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTInventLocationId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTItemId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTMovementId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalTime!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleDespatchDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleReg!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTWeightbridgeImportRef!xml]
FROM dbo.ABN_File_Header H JOIN dbo.Envelope E ON
H.Envelope_Id = E.Id
WHERE
H.DateTime_Transmit IS NULL

UNION 

SELECT 3 as Tag, 1 as Parent, 
                NULL as [Envelope!1!xmlns],
                NULL as [Header!2!MessageId!xml],
                NULL as [Header!2!SourceEndPointUser!xml],
                NULL as [Header!2!SourceEndPoint!xml],
                NULL as [Header!2!DestinationEndPoint!xml],
                NULL as [Header!2!Action!xml],
                NULL as [Body!3!WeightbridgeImport!xml],
        NULL as [WeightbridgeImport!4!Id!hide],
                NULL as [WeightbridgeImport!4!xmlns],
                NULL as [WeightbridgeImport!4!DocPurpose!xml],
                NULL as [WeightbridgeImport!4!SenderId!xml],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportTable!xml],
                NULL as [VCTWeightbridgeImportTable!5!Class],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportRef!xml],
                NULL as [VCTWeightbridgeImportLine!6!Class],
                NULL as [VCTWeightbridgeImportLine!6!VCTComments!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTInventLocationId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTItemId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTMovementId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalTime!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleDespatchDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleReg!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTWeightbridgeImportRef!xml]
FROM dbo.ABN_File_Header H JOIN dbo.ABN_File_Body D ON
H.Header_Id = D.Body_Id 
WHERE
D.DateTime_Transmit IS NULL

UNION 

SELECT 4 as Tag, 3 as Parent, 
                 NULL as [Envelope!1!xmlns],
                 NULL as [Header!2!MessageId!xml],
            NULL as [Header!2!SourceEndPointUser!xml],
            NULL as [Header!2!SourceEndPoint!xml],
                 NULL as [Header!2!DestinationEndPoint!xml],
                 NULL as [Header!2!Action!xml],
                 NULL as [Body!3!WeightbridgeImport!xml],
                 NULL as [WeightbridgeImport!4!Id!hide],
                D.WeightbridgeImport as [WeightbridgeImport!4!xmlns],
            D.DocPurpose as [WeightbridgeImport!4!DocPurpose!xml],
                D.SenderId as [WeightbridgeImport!4!SenderId!xml],                
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportTable!xml],
                NULL as [VCTWeightbridgeImportTable!5!Class],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportRef!xml],
                NULL as [VCTWeightbridgeImportLine!6!Class],
                NULL as [VCTWeightbridgeImportLine!6!VCTComments!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTInventLocationId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTItemId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTMovementId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalTime!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleDespatchDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleReg!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTWeightbridgeImportRef!xml]
FROM dbo.ABN_File_Header H JOIN dbo.ABN_File_Body D ON
H.Header_Id = D.Body_Id AND
D.DateTime_Transmit IS NULL
--ORDER BY D.Id

UNION 

SELECT 5 as Tag, 4 as Parent, 
                 NULL as [Envelope!1!xmlns],
                 NULL as [Header!2!MessageId!xml],
            NULL as [Header!2!SourceEndPointUser!xml],
            NULL as [Header!2!SourceEndPoint!xml],
                 NULL as [Header!2!DestinationEndPoint!xml],
                 NULL as [Header!2!Action!xml],
                 NULL as [Body!3!WeightbridgeImport!xml],
                 NULL as [Body!4!Id!hide],
                NULL as [WeightbridgeImport!4!xmlns],
            NULL as [WeightbridgeImport!4!DocPurpose!xml],
                NULL as [WeightbridgeImport!4!SenderId!xml],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportTable!],
                D.VCTWeightbridgeImportTable as [VCTWeightbridgeImportTable!5!Class],
                D.VCTWeightbridgeImportRef as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportRef!],
                NULL as [VCTWeightbridgeImportLine!6!Class],
                NULL as [VCTWeightbridgeImportLine!6!VCTComments!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTInventLocationId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTItemId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTMovementId!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTTicketNumberOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalTime!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleDespatchDate!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleReg!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightIn!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTVehicleWeightOut!xml],
                NULL as [VCTWeightbridgeImportLine!6!VCTWeightbridgeImportRef!xml]
FROM dbo.ABN_File_Header H JOIN dbo.ABN_File_Body D ON
H.Header_Id = D.Body_Id AND
D.DateTime_Transmit IS NULL
--ORDER BY [WeightbridgeImport!4!Id!hide]

UNION 

SELECT TOP 1 6 as Tag, 5 as Parent, 
        NULL as [Envelope!1!xmlns],
        NULL as [Header!2!MessageId!xml],
            NULL as [Header!2!SourceEndPointUser!xml],
            NULL as [Header!2!SourceEndPoint!xml],
        NULL as [Header!2!DestinationEndPoint!xml],
        NULL as [Header!2!Action!xml],
        NULL as [Body!3!WeightbridgeImport!xml],
        D.Id as [Body!4!Id!hide],
                NULL as [WeightbridgeImport!4!xmlns],
            NULL as [WeightbridgeImport!4!DocPurpose!xml],
                NULL as [WeightbridgeImport!4!SenderId!xml],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportTable!],
                NULL as [VCTWeightbridgeImportTable!5!Class],
                NULL as [VCTWeightbridgeImportTable!5!VCTWeightbridgeImportRef!xml],
                D.VCTWeightbridgeImportLine as [VCTWeightbridgeImportLine!6!Class],
                D.VCTComments as [VCTWeightbridgeImportLine!6!VCTComments!xml],
                D.VCTInventLocationId as [VCTWeightbridgeImportLine!6!VCTInventLocationId!xml],
                D.VCTItemId as [VCTWeightbridgeImportLine!6!VCTItemId!xml],
                D.VCTMovementId as [VCTWeightbridgeImportLine!6!VCTMovementId!xml],
                D.VCTTicketNumberIn as [VCTWeightbridgeImportLine!6!VCTTicketNumberIn!xml],
                D.VCTTicketNumberOut as [VCTWeightbridgeImportLine!6!VCTTicketNumberOut!xml],
                D.VCTVehicleArrivalDate as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalDate!xml],
                D.VCTVehicleArrivalTime as [VCTWeightbridgeImportLine!6!VCTVehicleArrivalTime!xml],
                D.VCTVehicleDespatchDate as [VCTWeightbridgeImportLine!6!VCTVehicleDespatchDate!xml],
                D.VCTVehicleReg as [VCTWeightbridgeImportLine!6!VCTVehicleReg!xml],
                D.VCTVehicleWeightIn as [VCTWeightbridgeImportLine!6!VCTVehicleWeightIn!xml],
                D.VCTVehicleWeightOut as [VCTWeightbridgeImportLine!6!VCTVehicleWeightOut!xml],
                D.VCTWeightbridgeImportRef as [VCTWeightbridgeImportLine!6!VCTWeightbridgeImportRef!xml]
FROM dbo.ABN_File_Header H JOIN dbo.ABN_File_Body D ON
H.Header_Id = D.Body_Id AND
D.DateTime_Transmit IS NULL
ORDER BY [WeightbridgeImport!4!Id!hide]
FOR XML EXPLICIT
Also, I need to perform this for each record in dbo.ABN_File_Body where
dbo.ABN_File_Header.Header_Id = dbo.ABN_File_Body.Body_Id AND
dbo.ABN_File_Body.DateTime_Transmit IS NULL

Each time a .xml file is produced, I need to update the dbo.ABN_File_Body file (SET DateTime_Transmit = GetDate())

Please note: The dbo.ABN_File_Body table is ordered by 'Id' (Primary Key) - I use TOP 1 to select the current record in the above query.

Any ideas please?

Thanks in advance,


Neal

A Northern Soul
__________________
Neal

A Northern Soul
Reply With Quote