Solved

PHYSICAL XML FILE FROM SQL SERVER

Posted on 2011-09-14
14
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 60

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 60

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 60

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
 

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 60

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 60

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 60

Expert Comment

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

Kevin
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 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