How to Create a Report in SSRS from Local XML File

Hi

i need to create a Report from XML file which available in my current folder.

can anybody suggest How to create a Data source from XML file and How to make a query for that.

Thanks

Mahesh

Are you doing this in an application with ReportViewer in LocalMode? Or is this going to be a server-side app (where you're just using the XML file in your folder as a test file)?

The way to do it is different, depending on the answer to this first question... but in both cases I will have the followup question:

Is the XML file in a regular, table-like format (like Dataset XML), so that it will be easy to make into a datasource? Or is the schema different altogether?

>L<

|||

Hi thanks for ur reply

now we have chenged the way i need to prepare a report

One web service will return a DataSet

based on that Dataset i need prepare a report.

Thanks

Mahesh

|||

hi Mahesh,

That change doesn't really change anything about my first question, although it answers the second one<g>.

The way you are going to do this is still going to be different depending on whether the report is rendered server-side or client-side. Are you writing a web-application, a win-forms application, or what? Are you using the reportviewer client control?

If you need to send that dataset as a source to a server-side rendering of the report, you are still going to have to send that information as XML to the server. (But that will be easy because the dataset will turn itself into the "right kind" of XML for this purpose.) If you need to do this, we will discuss further.

If you are rendering the report using reportviewer, so you can use local report mode, then you can almost certainly give the localmode report your dataset without any trouble, and without turning it into XML first. In fact, if you *are* using XML, you load it into a dataset to give to the viewer control <s>.

Hope this helps

>L<

|||

Hi Lisa

Thanks for giving more information

I need to prepare a Server side Report .

with sql server 2005 we got One VS 2005 IDE in that i have taken Report Server Project Here am creating my reports.

for this they are given me a webservice link that will return a Data set

based on that i need to prepare a Report.

Previously i know how to prepare a report from Sql server Database.

Thanks.

Mahesh

|||

OK. If the webservice returns a dataset, then it returns it as an XML message. You should be fine with this in a server-side report.

Define the datasource as follows:

The DataSource is of type XML.
The connection string for the web service datasource is in fact the URL for the web service! (http://...) For testing you can put up a local copy of it (http://localhost/test/my.xml) and assign that as your datasource.
The query string, believe it or not, can just be "*". (no quotes) if the XML is a "raw" dataset style. You can also use the (bleh) element query syntax that they've specified -- see here http://technet.microsoft.com/en-us/library/ms365158.aspx -- If you get stuck, post an example of what the web service response message looks like and I'll try to help.|||

Hi

Lisa

i was struck with writing query

i got the webservice responce

<?xml version="1.0" encoding="utf-8" ?>
- <DataSet xmlns="http://tempuri.org/">
- <xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
- <xs:complexType>
- <xs:choice minOccurs="0" maxOccurs="unbounded">
- <xs:element name="Project">
- <xs:complexType>
- <xsequence>
<xs:element name="ProjectUID" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="ProjectName" type="xstring" minOccurs="0" />
</xsequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>
- <diffgriffgram xmlns:msdata="urnchemas-microsoft-com:xml-msdata" xmlnsiffgr="urnchemas-microsoft-com:xml-diffgram-v1">
- <NewDataSet xmlns="">
- <Project diffgr:id="Project1" msdata:rowOrder="0">
<ProjectUID>8f584354-259b-48fb-a82b-838dd0d75a52</ProjectUID>
<ProjectName>Reports</ProjectName>
</Project>
</NewDataSet>
</diffgriffgram>
</DataSet>

In DataSet dialog Box i have taken a query

first i tried with "*" only it is showing error

an tried with this

<Query>
<SoapAction>
http://tempuri.org/GetAttributes
</SoapAction>
<Method Namespace="http://tempuri.org/"
Name="GetAttributes">
</Method>
</Query>

It is also not displaying the values of ProjectUid and ProjectName.

can u help me how to write the query for this type of WebService Responce.

Thanks

Mahesh

|||

Mahesh, I thought they were sending you a dataset -- is the method GetAttributes the way you are supposed to get that dataset or does it represent a query you are trying to do to get the metadata *describing* the dataset?

Either way... To use the dataset "raw", the thing you are receiving should look like, well, a serialized dataset, like you would get from a dataset if you used its .WriteXML() or .GetXML() methods. You can't use the "*" (I don't think) unless it looks like that. I can't remember what MS blog I read about this in -- it works, but it is only the simplest way.

It is not the only way. Here is a very quick walkthrough that you should find helpful for your scenario: http://blogs.msdn.com/bimusings/archive/2006/03/24/560026.aspx

Here is another walkthrough http://msdn.technetweb3.orcsweb.com/gsnowman/archive/2005/10/12/480321.aspx

And a blog entry that goes through it again http://blogs.msdn.com/bwelcker/archive/2005/11/13/492296.aspx

I think they both show you examples of query syntax.

Here is a tutorial about using XML as a datasource that will help you -- I'm pointing you at lesson 2, where you're learning how the web service is supposed to return the serialized data set but you probably want to look at all the lessons in this tutorial -- http://technet.microsoft.com/en-us/library/aa337489.aspx

Other references describing other aspects that I have found helpful.
http://technet.microsoft.com/en-us/library/aa964129.aspx
http://channel9.msdn.com/ShowPost.aspx?PostID=137650

>L<

How to Create a Report in SSRS from Local XML File

Hi

i need to create a Report from XML file which available in my current folder.

can anybody suggest How to create a Data source from XML file and How to make a query for that.

Thanks

Mahesh

Are you doing this in an application with ReportViewer in LocalMode? Or is this going to be a server-side app (where you're just using the XML file in your folder as a test file)?

The way to do it is different, depending on the answer to this first question... but in both cases I will have the followup question:

Is the XML file in a regular, table-like format (like Dataset XML), so that it will be easy to make into a datasource? Or is the schema different altogether?

>L<

|||

Hi thanks for ur reply

now we have chenged the way i need to prepare a report

One web service will return a DataSet

based on that Dataset i need prepare a report.

Thanks

Mahesh

|||

hi Mahesh,

That change doesn't really change anything about my first question, although it answers the second one<g>.

The way you are going to do this is still going to be different depending on whether the report is rendered server-side or client-side. Are you writing a web-application, a win-forms application, or what? Are you using the reportviewer client control?

If you need to send that dataset as a source to a server-side rendering of the report, you are still going to have to send that information as XML to the server. (But that will be easy because the dataset will turn itself into the "right kind" of XML for this purpose.) If you need to do this, we will discuss further.

If you are rendering the report using reportviewer, so you can use local report mode, then you can almost certainly give the localmode report your dataset without any trouble, and without turning it into XML first. In fact, if you *are* using XML, you load it into a dataset to give to the viewer control <s>.

Hope this helps

>L<

|||

Hi Lisa

Thanks for giving more information

I need to prepare a Server side Report .

with sql server 2005 we got One VS 2005 IDE in that i have taken Report Server Project Here am creating my reports.

for this they are given me a webservice link that will return a Data set

based on that i need to prepare a Report.

Previously i know how to prepare a report from Sql server Database.

Thanks.

Mahesh

|||

OK. If the webservice returns a dataset, then it returns it as an XML message. You should be fine with this in a server-side report.

Define the datasource as follows:

The DataSource is of type XML.
The connection string for the web service datasource is in fact the URL for the web service! (/) For testing you can put up a local copy of it (http://localhost/test/my.xml) and assign that as your datasource.
The query string, believe it or not, can just be "*". (no quotes) if the XML is a "raw" dataset style. You can also use the (bleh) element query syntax that they've specified -- see here http://technet.microsoft.com/en-us/library/ms365158.aspx -- If you get stuck, post an example of what the web service response message looks like and I'll try to help.|||

Hi

Lisa

i was struck with writing query

i got the webservice responce

<?xml version="1.0" encoding="utf-8" ?>
- <DataSet xmlns="http://tempuri.org/">
- <xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
- <xs:complexType>
- <xs:choice minOccurs="0" maxOccurs="unbounded">
- <xs:element name="Project">
- <xs:complexType>
- <xsequence>
<xs:element name="ProjectUID" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="ProjectName" type="xstring" minOccurs="0" />
</xsequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>
- <diffgriffgram xmlns:msdata="urnchemas-microsoft-com:xml-msdata" xmlnsiffgr="urnchemas-microsoft-com:xml-diffgram-v1">
- <NewDataSet xmlns="">
- <Project diffgr:id="Project1" msdata:rowOrder="0">
<ProjectUID>8f584354-259b-48fb-a82b-838dd0d75a52</ProjectUID>
<ProjectName>Reports</ProjectName>
</Project>
</NewDataSet>
</diffgriffgram>
</DataSet>

In DataSet dialog Box i have taken a query

first i tried with "*" only it is showing error

an tried with this

<Query>
<SoapAction>
http://tempuri.org/GetAttributes
</SoapAction>
<Method Namespace="http://tempuri.org/"
Name="GetAttributes">
</Method>
</Query>

It is also not displaying the values of ProjectUid and ProjectName.

can u help me how to write the query for this type of WebService Responce.

Thanks

Mahesh

|||

Mahesh, I thought they were sending you a dataset -- is the method GetAttributes the way you are supposed to get that dataset or does it represent a query you are trying to do to get the metadata *describing* the dataset?

Either way... To use the dataset "raw", the thing you are receiving should look like, well, a serialized dataset, like you would get from a dataset if you used its .WriteXML() or .GetXML() methods. You can't use the "*" (I don't think) unless it looks like that. I can't remember what MS blog I read about this in -- it works, but it is only the simplest way.

It is not the only way. Here is a very quick walkthrough that you should find helpful for your scenario: http://blogs.msdn.com/bimusings/archive/2006/03/24/560026.aspx

Here is another walkthrough http://msdn.technetweb3.orcsweb.com/gsnowman/archive/2005/10/12/480321.aspx

And a blog entry that goes through it again http://blogs.msdn.com/bwelcker/archive/2005/11/13/492296.aspx

I think they both show you examples of query syntax.

Here is a tutorial about using XML as a datasource that will help you -- I'm pointing you at lesson 2, where you're learning how the web service is supposed to return the serialized data set but you probably want to look at all the lessons in this tutorial -- http://technet.microsoft.com/en-us/library/aa337489.aspx

Other references describing other aspects that I have found helpful.
http://technet.microsoft.com/en-us/library/aa964129.aspx
http://channel9.msdn.com/ShowPost.aspx?PostID=137650

>L<

How to create a Folder using a SQL Query?

Hi

How can I create a Folder (eg. C:\MyNewFolder) using SQL Query?

EXEC xp_cmdshell 'MD C:\MyNewFolder'

Chris

|||

hey ..u can create a file using FSO ..n operate further..

http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

|||

hey chris .

i tried this in my query analyzer of sql2000 its not wrking..

the error is..

Could not find stored procedure 'xp_cmdshell'. even i tried using dbo.xp_cmdshell not wrking..

reply plz..

thanks

|||

All Extended Procedures reside in the master database, so try this:

EXEC master.dbo.xp_cmdshell 'MD C:\MyNewFolder'

Chris

|||

Chris Howarth wrote:

EXEC xp_cmdshell 'MD C:\MyNewFolder'

Chris

Thanks Chris

It work perfect, Im using SQL EXPRESS 2005

|||

Is there a way to do this?

Code Snippet

declare @.Location nvarchar(50)

set @.Location = 'C:\mynewfolder'

EXEC master.dbo.xp_cmdshell 'MD ' + @.Location

Im getting the following Error

Code Snippet

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '+'.

|||I tried your code in my SQL Server, I think he doesn't like the concatenation as a parameter...

try to set your string to a variable

Code Snippet

declare @.cmdpath nvarchar(60)
set @.cmdpath = 'MD '+ @.Location
exec master.dbo.xp_cmdshell @.cmdpath

Regards

|||

celobateira wrote:

I tried your code in my SQL Server, I think he doesn't like the concatenation as a parameter...

try to set your string to a variable

Code Snippet

declare @.cmdpath nvarchar(60)
set @.cmdpath = 'MD '+ @.Location
exec master.dbo.xp_cmdshell @.cmdpath

Regards

Its working, thank you

|||

Hi,

I added some code in order to get the result from the xp_cmdshell command

This returns null if successfull, if an error occurs returns the error message. May be useful instead of getting an sql error

Code Snippet

declare @.cmdpath nvarchar(60), @.Location nvarchar(100), @.message nvarchar(max)

set @.Location = N'C:\Temp\Temp5'

set @.cmdpath = 'MD '+ @.Location

Create table #result

(

result nvarchar(255)

)

insert into #result (result) exec master.dbo.xp_cmdshell @.cmdpath

select @.message = ISNULL(@.message + ' - ','') + result from #result where result is not null

select @.message

drop table #result

Eralper

http://www.kodyaz.com

|||

thxx chris.. its working ....

thx a lott

How to copy..........

I have 2 files with its same name (at folder C:\a\ and C:\b\, both Name
files is SData.mdf). I wanna access the files in enterprise manager at the
time. Can I do it? Because I wanna copy a object (table, store procedure,
etc) from C:\a\Sdata.mdf to C:\b\Sdata.mdf.
How to do it efficiently?Hi
You cannot 'access/open' them . Its data files SQL Server used for. RESTORE
DATABASE with a different names and make your copies
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:eLt2paOTGHA.4384@.tk2msftngp13.phx.gbl...
>I have 2 files with its same name (at folder C:\a\ and C:\b\, both Name
> files is SData.mdf). I wanna access the files in enterprise manager at the
> time. Can I do it? Because I wanna copy a object (table, store procedure,
> etc) from C:\a\Sdata.mdf to C:\b\Sdata.mdf.
> How to do it efficiently?
>

How to copy MSSQL DB

Hi

I'm a newbie with MSSQL. I hv MSSQL 7.0 running successfully on my notebook as a test environment & the folder is \MSSQL7\DATA. We have a production server running live. There are 2 files called recruitment.mdf & recruitment_log.ldf which resides on the server but is not in my notebook. When i copy it to my notebook, the Enterprise Mgr does not recognise it. It does not show in the database list. Why? What shld I do?? I wud appreciate step-by-step instructions. ThanksWhat do u mean "Enterprise Mgr does not recognise it"? U just copied the mdf and ldf files, or u have attached the database but still not show up at Enterprise Manager, or u even failed to attach it?|||please see the sp_attach_db and sp_detach_db system stored procedures in sql server book online. You can also do a backup and restore using the replace argument of the RESTORE DATABASE command over a blank database. The former is what I usually do. I do not think SQL 7 had the copy database wizard (that was so long ago) but if it does, stay away from it. It's a pain.

How to copy database to another SQL server

Hello guys,

I need to copy a database in SQL 2000 to another SQL2000 server. I tried backing up the database to a folder then restore it but it's not happening. Please help to know how to copy database to another SQL server, or a disk and restore it to another SQL server.I don't know whether this was discussed before If it was, please forgive me.

Thanks in advance.What error you got when you tried to restore from backup?

Other way is to script entire database schema and run it on another server to create empty database structure and then using either BCP/DTS to populate data.|||Hello,

Stop the sql services of a source server, copy .mdf & .ldf file to target server & then attach the db in target server as follows
1. In Enterprise manager, right click on databases & go in all tasks & then attach db
2. select .mdf file from the copied location & attach it

u will see db is copied.

Shubhangi