Database

30-Oct-09 4:59

Good Day All

i have the Following Query that Procudes an XML

DECLARE @CurrentTime DATETIME
SET @CurrentTime = CURRENT_TIMESTAMP
select tr.Descr [Room], tb.Purpose [Purpose], tb.Description [Description],
convert(varchar,datepart(hour,tb.starttime))+':'+convert(varchar,datepart(minute,tb.starttime)) [Start Time],
convert(varchar,datepart(hour,tb.endtime))+':'+convert(varchar,datepart(minute,tb.endtime)) [End Time],
tu.name [Requested by]
from tbl_booking tb inner join tbl_resource tr
on tb.resources = tr.id
inner join tbl_user tu on tu.id = tb.RequestedByUser
where (day(startdate) = day(@CurrentTime))and(month(startdate)=month(@CurrentTime))and(year(startdate)=year(@CurrentTime))and(tb.status=1)
order by [Room],[Start Time]
FOR xml raw

i want to Store the File as "XMLDoc1.XML" in my C:\

Kind Regards

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

J4amieC30-Oct-09 5:54

30-Oct-09 5:54

Vuyiswa Maseko wrote:
i want to Store the File as "XMLDoc1.XML" in my C:\

And where is the problem?

Vimalsoft(Pty) Ltd30-Oct-09 5:55

30-Oct-09 5:55

In Management Studio this will show the results, but i want to store this results in my C:\. i want to BCP.

Thanks

J4amieC30-Oct-09 7:09

30-Oct-09 7:09

So whats the problem highlighting the XML in management studio right click>Save as...?

Failing that, have a look at SqlCmd from a command prompt. It allows you to execute a query, and output the results to a file.

Vimalsoft(Pty) Ltd1-Nov-09 20:05

1-Nov-09 20:05

i understand what you are Saying. But i want to do that from the above Query. not in C# nor SQL Management Studio

wadhva_maninder30-Oct-09 20:03

wadhva_maninder

30-Oct-09 20:03

You can use bcp to create xml in shared folder with Everyone rights and then can use FILE class to move to C:\ drive

Vimalsoft(Pty) Ltd1-Nov-09 20:06

1-Nov-09 20:06

That is Good. Can you please Provide an Example based on the above query ?

Andy_L_J30-Oct-09 21:43

Andy_L_J

30-Oct-09 21:43

You can right_click in the results pane in SSMS and select save result as...

Change the filename and extension and save.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Vimalsoft(Pty) Ltd1-Nov-09 20:03

1-Nov-09 20:03

i could get this in C# or doing a right click as you say, but i want the statement to create a File on the local drive

J4amieC1-Nov-09 21:43

1-Nov-09 21:43

SQL Statements are for extracting data from a database. As you know. IMO it is some client application which is responsible for doing somethign with that data, like displaying it, ort in your case, outputting it to a file.

Im interested however, why you think you want to do this all with a SQL statement? Something (a process, an application, maybe SQL Agent) must be executing that statement....

Vimalsoft(Pty) Ltd1-Nov-09 22:15

1-Nov-09 22:15

My client has a new Client that need to get this XML to be somewhere in the local drive. They are using SQl 2000 and am limited in a way. So with the above query i just need to save what is output in the local drive

J4amieC2-Nov-09 1:15

Re: its urgent [modified]

2-Nov-09 1:15

Vuyiswa Maseko wrote:
My client has a new Client that need to get this XML to be somewhere in the local drive

So write youself a utility (Console App, Service, Windows App, VB Script) which executes your query and writes the result to the filesystem.

Easy.

its urgent

bcss03a00629-Oct-09 21:00

bcss03a006

29-Oct-09 21:00

I have a table with two columns
1- id
2- parent_id

I want a SQL query that take parent_id as perameter and retrieve all its children and children of children and so on upto N level.
Data in table like:
ID parent_id
S1 SG
G1 FG
GP01 FG
GP0101 GP01
G11 G1
G12 G1
G111 G11
G112 G11
G113 G11

If I pass FG as parent_id, the query should return G1,GP01,GP0101,G11,G12,G111,G112,G113.

Please help me in this regard

Niladri_Biswas29-Oct-09 21:46

Niladri_Biswas

29-Oct-09 21:46

This kind of problem can be easily solved with Recursive CTE's.

Try this.
declare @tbl table(id varchar(20),parentid varchar(20))
insert into @tbl
select 'S1','SG' union all
select 'G1','FG' union all
select 'GP01','FG' union all
select 'GP0101','GP01' union all
select 'G11','G1' union all
select 'G12','G1' union all
select 'G111','G11' union all
select 'G112','G11' union all
select 'G113','G11'

;with cte as
(
	select t1.parentid,t1.id,0 AS [Level] from  @tbl t1	
	where t1.parentid  = 'FG'	
	union all 	
	select t1.parentid,t1.id,[Level]+1 from  @tbl t1
	inner join cte c
	on c.id = t1.parentid
)
select left(Decendants,len(Decendants)-1) Decandants
from
    (
		select id  + ','
		from cte
		for xml path ('')
    ) Result(Decendants)

Pass the parent id as a parameter from ur stored proc.

O/P:

Decandants
G1,GP01,GP0101,G11,G12,G111,G112,G113

Niladri Biswas

modified on Friday, October 30, 2009 6:11 AM

Re: its urgent

Mycroft Holmes29-Oct-09 22:13

Mycroft Holmes

29-Oct-09 22:13

Niladri_Biswas wrote:
Note- I sql server 2008, for solving this kind of problem you can take the advantage of Hierarchial Id's

Can 2008 use this format as a hierarchyID, there us no delimiter for the levels, the standard HID.ToString() looks like '/G12/1/'

Convert Oracle Query to MS-Access

M Riaz Bashir29-Oct-09 20:22

M Riaz Bashir

29-Oct-09 20:22

Hi,

Kindly let me know how may I convert following Oracle sql into MS-Access ?

SELECT A.SNO, A.CODE, A.QTY, A.RATE, B.NAME
FROM DETAIL A, MASTER B
WHERE A.CODE=B.CODE(+)

thanx in advance

Mycroft Holmes29-Oct-09 22:10

Mycroft Holmes

29-Oct-09 22:10

This should wok, I'm not sure what the significance of (+) is in the join

SELECT A.SNO, A.CODE, A.QTY, A.RATE, B.NAME
FROM DETAIL A
	INNER JOIN MASTER B ON A.CODE=B.CODE

Jörgen Andersson29-Oct-09 23:45

Jörgen Andersson

29-Oct-09 23:45

The (+) makes it an outer join.

Jörgen Andersson29-Oct-09 23:05

Jörgen Andersson

29-Oct-09 23:05

M Riaz Bashir wrote:
Kindly let me know how may I convert following Oracle sql into MS-Access ?

Why, would anyone ever move from oracle to access?
Anyway, here goes.

SELECT A.SNO, A.CODE, A.QTY, A.RATE, B.NAME
FROM DETAIL A RIGHT JOIN MASTER B
WHERE A.CODE=B.CODE