XML to file

Question

Post reply

XML to file

inevercheckthis2002

SSCommitted

Points: 1819
More actions
July 24, 2014 at 12:47 pm

#74382

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!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

This is simple, run the query with "Results to Grid", right click on the results and select Save Results As..

😎

inevercheckthis2002 SSCommitted Points: 1819 More actions · Answer 2

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 SSC Guru Points: 182881 More actions · Answer 3

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.

😎

You might also want to add the TYPE directive to the query.

SELECT * FROM SomeTable1

FOR XML RAW ('DataRecord'), ROOT ('DataRecords'), TYPE, ELEMENTS;

inevercheckthis2002 SSCommitted Points: 1819 More actions · Answer 4

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 SSC Eights! Points: 817 More actions · Answer 5

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 SSCommitted Points: 1819 More actions · Answer 6

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.