Link to home
Start Free TrialLog in
Avatar of Sreedhar Vengala
Sreedhar VengalaFlag for Australia

asked on

Add declaration to XML file

Creating XML out of data in Database  by calling proc with bcp as
SET @SQL= 'bcp "exec TEST_433_Oaky_Creek.dbo.proc" queryout '+ @FileName +' -w -r -t  -Sdd\SQL2005 -T '
(proc produced below)
Everything is fine => creates XML as desired.
Now task is to Add Declaration to this XML (<?xml version="1.0" ?>)

How can this be achieved either in below proc or concating XML with other file (containing the declaration)
Attached is XML produced from proc.
Thanks
SELECT  ( SELECT TOP 1
                    ShiftDate AS "ShiftDate",
                    Shift AS "Shift"
          FROM      [TableName]
        FOR
          XML PATH(''),
              TYPE
        ),
        ( SELECT    EquipmentId AS "WasheryProductionDetails/EquipmentCode",
                    'n/a' AS "WasheryProductionDetails/ActivityCode",
                    'n/a' AS "WasheryProductionDetails/ReasonCode",
                    Parentmaterial AS "WasheryProductionDetails/WasheryFeed/MaterialCode",
                    ParentStockpile AS "WasheryProductionDetails/WasheryFeed/ROMStockpileCode",
                    CAST(ParentTonnes AS DECIMAL(18, 4)) AS "WasheryProductionDetails/WasheryFeed/FeedTonnes",
                    ChildMaterial AS "WasheryProductionDetails/WasheryOutput/MaterialCode",
                    ChildStockpile AS "WasheryProductionDetails/WasheryOutput/ProductStockpileCode",
                    CAST(ChildTonnes AS DECIMAL(18, 4)) AS "WasheryProductionDetails/WasheryOutput/ProductTonnes"
          FROM      [TableName]
        FOR
          XML PATH(''),
              TYPE
        )
FOR     XML PATH(''),
            ROOT('WasheryProduction')

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Chris M
Chris M
Flag of Uganda image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial