• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

PHYSICAL XML FILE FROM SQL SERVER

I have a stored procedure that outputs the results in XML Format.  I need to get the result into a physical file in XML format so that I can use it from an SSIS package

Is there some way that I can create a physical file that is readable by SSIS?  
0
sherbug1015
Asked:
sherbug1015
1 Solution
 
Richard QuadlingSenior Software DeveloperCommented:
sqlcmd may be the tool to use here.

An example of this would be something like
SQLCMD -E -Sservername -Q"EXECUTE master.dbo.sp_helpdb" -o "c:\filename.txt"

Open in new window




0
 
sherbug1015Author Commented:
I tried using SQLCMD but I continue to get errors.  I am not running SQL Server locally.  

Could I do something like this inside SQL Server.  

DECLARE
  @FileName VARCHAR(50),
  @SQLCmd   VARCHAR(500)
 
 
select
  @FileName = 'C:\SampleXMLOutput1.xml'
 
--in this command, we are making sure there is only one ROOT node

SELECT  @SQLCmd = 'bcp '
                + '"exec  PromonetATT_DEV.[dbo].[proc_Stratecash_Location]'                
                + ' FOR XML PATH(''Location''), ROOT(''Locations''), TYPE "'
                + ' queryout '  
                + @FileName
                + ' -w -T -S' + @@SERVERNAME
               
-- display command, for visual  check
SELECT @SQLCmd AS 'Command to execute'
-- create the XML file
EXECUTE master..xp_cmdshell @SQLCmd

Can I execute a stored procedure using bcp?


0
 
DrewKjellCommented:
Can you run your query as the first step of the SSIS package but use the FOR XML keywords to format the result as XML to use instead of needing to read in a file?

Drew
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Kevin CrossChief Technology OfficerCommented:
You can use the bcpUtility.
I wrote a blog post on the topic: http://daytabase.org/2011/08/20/create-xml-files-bcp/

The caveat in T-SQL is that bcp is a command-line utility, so it required xp-cmdshell. The benefit you have in SSIS, with my limited knowledge, is that you can call command-line processes via the Execute Process Task. You could basically execute a bcp command using SSIS. At least that is the theory.

If nothing else, you can use my Article to do this from T-SQL.

In perfect scenario, you can create a table as I showed:
CREATE TABLE xmlfeeds(
   xml_id NUMERIC(18,0) IDENTITY(1,1),
   xml_date DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP),
   xml_data XML NOT NULL,
   PRIMARY KEY(xml_id)
);

Open in new window


Then have your stored procedure return its XML data to this table:
INSERT INTO xmlfeeds(xml_data)
EXEC sp_your_stored_procedure;

Open in new window


Capture SCOPE_IDENTITY() from that.

Then use queryout example shown to get the XML file:
'bcp "SELECT xml_data FROM {your_db}..xmlfeeds WHERE xml_id ='+CONVERT(VARCHAR(18),@id)+'" queryout "{your_file_name}.xml" -T -c';

Open in new window


Where @id is the value of SCOPE_IDENTITY().

Hope that helps!
0
 
sherbug1015Author Commented:
mwvisa1

Your answer was most helpful.  When I output using bcp, is there a way that I can insert the XML Definition i.e.  <?xml version="1.0" encoding="UTF-8" ?>.  

Thanks.
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry, I got caught up with a conference call. Yes, you can just concatenate that to the beginning.
0
 
Kevin CrossChief Technology OfficerCommented:
i.e.,
SELECT '<?xml version="1.0" encoding="UTF-8" ?>'+CONVERT(VARCHAR(MAX), xml_data) ...

Open in new window


Additionally, if you are going this route, i.e., converting the stored XML to VARCHAR anyway, you can add in the nicety of replacing the '>' with ‘>’+CHAR(13)+CHAR(10) which will put each element of the XML on its own line in the resulting file. That doesn't totally make formatting pretty, but at least better than all on one line. :)
0
 
sherbug1015Author Commented:
mwvisa1

This is my bcp statement.  When I try to add the xml definition string I am not understanding how to get it into the Select statement.  How would I add it to my statement.  

Thanks...last time I will bother you.

SELECT  @SQLCmd = 'bcp '
                  + '" SELECT xml_data FROM PromonetATT_Dev.dbo.xmlfeeds WHERE xml_id =(Select Max(xml_id) from PromonetATT_Dev.dbo.xmlfeeds)" '
                          + ' queryout '  
                  + @FileName
                  + ' -w -T -S' + @@SERVERNAME
0
 
Kevin CrossChief Technology OfficerCommented:
Try like this, please:
SELECT  @SQLCmd = 'bcp '
                  + '" SELECT ''<?xml version="1.0" encoding="UTF-8" ?>''+CONVERT(VARCHAR(MAX),xml_data) FROM PromonetATT_Dev.dbo.xmlfeeds WHERE xml_id =(Select Max(xml_id) from PromonetATT_Dev.dbo.xmlfeeds)" '
                          + ' queryout '  
                  + @FileName
                  + ' -w -T -S' + @@SERVERNAME
0
 
Kevin CrossChief Technology OfficerCommented:
Those are two single quotes next to each other by the way and NOT double quotes:
''<?xml version="1.0" encoding="UTF-8" ?>''+CONVERT(VARCHAR(MAX),xml_data) 

Open in new window

0
 
sherbug1015Author Commented:
mwvisa1 - Thank you so much.  I am forever grateful for your excellent help.
0
 
sherbug1015Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for sherbug1015's comment http:/Q_27307993.html#36539890

for the following reason:

Super Job. &nbsp;Thanks.
0
 
sherbug1015Author Commented:
I accept my comments instead of the solution.
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome!
Best regards and happy coding,

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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