Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML to file Expand / Collapse
Author
Message
Posted Thursday, July 24, 2014 12:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 5:40 AM
Points: 80, Visits: 343
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!



Post #1595993
Posted Saturday, August 2, 2014 6:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 1,978, Visits: 5,146
This is simple, run the query with "Results to Grid", right click on the results and select Save Results As..
Post #1598928
Posted Thursday, August 14, 2014 8:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 5:40 AM
Points: 80, Visits: 343
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???
Post #1603289
Posted Thursday, August 14, 2014 9:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 1,978, Visits: 5,146
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;

Post #1603562
Posted Monday, August 18, 2014 9:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 5:40 AM
Points: 80, Visits: 343
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>
Post #1604531
Posted Tuesday, August 19, 2014 5:00 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:54 AM
Points: 25, Visits: 125
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).
Post #1604834
Posted Tuesday, August 19, 2014 5:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 5:40 AM
Points: 80, Visits: 343
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.

Post #1604850
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse