p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2005 (http://p2p.wrox.com/forumdisplay.php?f=220)
-   -   SQL XML Explicit (Output file required) (http://p2p.wrox.com/showthread.php?t=69838)

Neal August 4th, 2008 03:04 AM

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


All times are GMT -4. The time now is 04:53 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.