Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 889
  • Last Modified:

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

0
Sreedhar Vengala
Asked:
Sreedhar Vengala
1 Solution
 
Chris MConsulting - Technology ServicesCommented:
You may think of combining two result sets before the queryout.
Let the first one have the header as a string, then the second one will have the real XML file data.
Modify your procedure "TEST_433_Oaky_Creek.dbo.proc" to have the following SQL statement (without quotes)
"select '<?xml version="1.0" ?>
'"
then add the rest of the query.
Execute the proc the same way inside the bcp block.
Please note that after the closing tag, i have brocken the line to the next to create the same effect in the XML file output by the procedure since the line break will be treated as a string by the procedure.
Good day.
 
Regards,
Chris.

CREATE PROCEDURE [proc_name] AS
SELECT '<?xml version="1.0" ?>
'
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

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now