SQL XML explicit

• DerbyNeal

  Hall of Fame

  Points: 3854

  More actions

  August 1, 2008 at 4:36 am

  #382107

  Hi Guys,

  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

  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

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  August 28, 2008 at 6:07 am

  #864275

  Look up the BCP utility in Books Online.

  However, IMHO a much better option would be to design a client application to save the data to a file. This would give you more control over the entire operation.

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com