SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML to file


XML to file

Author
Message
inevercheckthis2002
inevercheckthis2002
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 474
I have a query that, when run in SSMS with 'Results to Grid' selected, provides a link (in the grid). When I click on the link, I see the results that I expect.

When I run the query with 'Results to text' or 'Results to file' selected, the text or file contains a line starting with the characters "XML..." and a second line containing dashes '--------------...'

The query is to be a monthly report, the output of which I need to email out.

How do I get a file that omits the first two lines that I can email out (with SSRS)?

Thanks!
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15438 Visits: 18622
This is simple, run the query with "Results to Grid", right click on the results and select Save Results As..
Cool
inevercheckthis2002
inevercheckthis2002
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 474
Allow me to illustrate


USE MyTempDB
GO

IF OBJECT_ID('MyTempDB..SomeTable1','U') IS NOT NULL
DROP TABLE SomeTable1;

CREATE TABLE SomeTable1
(
Firstname varchar(20),
Lastname varchar(20),
Position varchar(20)
);
GO

INSERT INTO SomeTable1
(Firstname, Lastname, Position)
SELECT 'Adam', 'Athome', 'President' UNION ALL
SELECT 'Dagwood', 'Bumstead', 'VicePresident'
GO



When I run this:


SELECT * FROM SomeTable1
FOR XML RAW ('DataRecord'), ROOT ('DataRecords'), ELEMENTS;



..with "results to file" selected, I get this:


XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<DataRecords><DataRecord><Firstname>Adam</Firstname><Lastname>Athome</Lastname><Position>President</Position></DataRecord><DataRecord><Firstname>Dagwood</Firstname><Lastname>Bumstead</Lastname><Position>VicePresident</Position></DataRecord></DataRecords>
(2 row(s) affected)



I'm trying to produce a file with just the XML:


<DataRecords>
<DataRecord>
<Firstname>Adam</Firstname>
<Lastname>Athome</Lastname>
<Position>President</Position>
</DataRecord>
<DataRecord>
<Firstname>Dagwood</Firstname>
<Lastname>Bumstead</Lastname>
<Position>VicePresident</Position>
</DataRecord>
</DataRecords>



I do not want this part in the XML file:
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



..or this part:

(2 row(s) affected)



I just want a file to email out. I'd like to schedule the email with SSRS (no user intervention).

Any ideas???
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15438 Visits: 18622
It is SSMS which is playing tricks on you, for an automatic process it should not be in the picture. Create a stored procedure with your select statement and call it from SSRS to retrieve the XML.
Cool
You might also want to add the TYPE directive to the query.
SELECT * FROM SomeTable1
FOR XML RAW ('DataRecord'), ROOT ('DataRecords'), TYPE, ELEMENTS;


inevercheckthis2002
inevercheckthis2002
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 474
Going from bad to worse...

Whether I use the query directly in a report, or put the query in a stored procedure, this is the result:

<?xml version="1.0" encoding="utf-8"?><Report xsi:schemaLocation="SQLTestServerTest2 http://SQLReportServer/ReportServer?%2FSQLTestServerTest2&rs%3AFormat=XML&rs%3ASnapshot%3Aisnull=True&rc%3ASchema=True" Name="SQLTestServerTest2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="SQLTestServerTest2">
<table1>
<Detail_Collection>
<Detail XML_F52E2B61_18A1_11d1_B105_00805F49916B="<DataRecords><DataRecord><Firstname>Adam</Firstname><Lastname>Athome</Lastname><Position>President</Position></DataRecord><DataRecord><Firstname>Dagwood</Firstname><Lastname>Bumstead</Lastname><Position>VicePresident</Position></DataRecord></DataRecords>" /></Detail_Collection>
</table1>
</Report>


I still have this funkyness:
XML_F52E2B61_18A1_11d1_B105_00805F49916B


..and now everything is in a <Detail Collection> ..and a <Table>
Tavis Reddick
Tavis Reddick
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 191
I am not sure about the report, but the SQL Server Management Studio issue seems to be that you are saving the results (possibly as text or CSV), not the XML column within them. If I want to save XML from SSMS results, I click the XML column (it is usually blue and underlined) which opens the XML in a new query window which can be saved as XML (without the usual declaration, but this usually works in applications).
inevercheckthis2002
inevercheckthis2002
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 474
Tavis Reddick (8/19/2014)
I am not sure about the report, but the SQL Server Management Studio issue seems to be that you are saving the results (possibly as text or CSV), not the XML column within them. If I want to save XML from SSMS results, I click the XML column (it is usually blue and underlined) which opens the XML in a new query window which can be saved as XML (without the usual declaration, but this usually works in applications).


Yes, that works fine, but how to automate an email with the XML in a file as an attachment is the question.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search