Solved

PHYSICAL XML FILE FROM SQL SERVER

Posted on 2011-09-14
14
364 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 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

15 Experts available now in Live!

Get 1:1 Help Now