Solved

PHYSICAL XML FILE FROM SQL SERVER

Posted on 2011-09-14
14
367 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

How our DevOps Teams Maximize Uptime

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 67
Parsing this XML works but the other one doesn't 9 35
Powershell Regex Replace Question 5 41
Help with SQL pivot 11 49
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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