Solved

PHYSICAL XML FILE FROM SQL SERVER

Posted on 2011-09-14
14
363 Views
Last Modified: 2012-08-14
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
Comment
Question by:sherbug1015
14 Comments
 
LVL 40

Expert Comment

by:RQuadling
ID: 36537347
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
 

Author Comment

by:sherbug1015
ID: 36537553
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
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36537960
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36538076
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
 

Author Comment

by:sherbug1015
ID: 36538357
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36539050
Sorry, I got caught up with a conference call. Yes, you can just concatenate that to the beginning.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36539084
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:sherbug1015
ID: 36539612
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36539666
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36539674
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
 

Author Comment

by:sherbug1015
ID: 36539890
mwvisa1 - Thank you so much.  I am forever grateful for your excellent help.
0
 

Author Comment

by:sherbug1015
ID: 36539903
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
 

Author Comment

by:sherbug1015
ID: 36539904
I accept my comments instead of the solution.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36539925
You are most welcome!
Best regards and happy coding,

Kevin
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now