Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export XML using TSQL Query

Status
Not open for further replies.

Kevinillingw

Technical User
Aug 2, 2004
15
GB
Guys

I am export data from my database to and xml file when I run this as a query in SQL Studio it shows the formatting correct see the example below
SQL Select
SELECT [PartNum] AS [BatchData/ProcessName]
,'' AS 'BatchData/ProcessRevision'
,[JobNum] AS [BatchData/Name]
,[PartDescription] AS [BatchData/Description]
,'' AS 'BatchData/Order',
replace(convert(NVARCHAR, [StartDate], 120), '-', '/') as 'BatchData/ActivationDate',
replace(convert(NVARCHAR, [DueDate], 120), '-', '/') as 'BatchData/ShipDate'
,'CONNORPANTEMP' AS 'BatchData/PanelBarcodeTemplate'
,'CONNORIMGTEMP' AS 'BatchData/ImageBarcodeTemplate'
,'ERROR' AS 'BatchData/QuantityEnforcement'
,[JobNum] AS [BatchData/Lot]
,'' AS 'BatchData/Approval'
,'' AS 'BatchData/StartingSerial'
,'' AS 'BatchData/EndingSerial'
,cast([prodqty] as integer)AS [BatchData/Quantity]
FROM [EpicorTest905].[dbo].[JobHead]
WHERE JobReleased = '1' AND JobFirm = '1' AND JobComplete = '0'
FOR XML PATH ('Batches');

Result

<Batches>
<BatchData>
<ProcessName>AC1457</ProcessName>
<ProcessRevision></ProcessRevision>
<Name>F015014</Name>
<Description>PCB Assembly, ASC Front Output Board (STAMP 4out Build Type 1</Description>
<Order></Order>
<ActivationDate>2014/11/10 00:00:00</ActivationDate>
<ShipDate>2014/11/14 00:00:00</ShipDate>
<PanelBarcodeTemplate>CONNORPANTEMP</PanelBarcodeTemplate>
<ImageBarcodeTemplate>CONNORIMGTEMP</ImageBarcodeTemplate>
<QuantityEnforcement>ERROR</QuantityEnforcement>
<Lot>F015014</Lot>
<Approval></Approval>
<StartingSerial></StartingSerial>
<EndingSerial></EndingSerial>
<Quantity>20</Quantity>
</BatchData>
</Batches>

But yet when I schedule this query as a job it adds a line break every 512 characters and doesn't format the data like above all on one line can anyone help ?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top