| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
09-07-04, 11:37
|
|
Registered User
|
|
Join Date: Sep 2004
Posts: 7
|
|
Using FOR XML Explicit within a DTS package
|
|
I created a SELECT statement that uses the FOR XML EXPLICIT clause so that SQL Server data can be exported to another system. Format thios system accepts is XML. What should I define within a DTS package so that this Select statement is executed and an XML file is created?
|
|
09-07-04, 12:02
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
|
|
09-07-04, 12:35
|
|
Registered User
|
|
Join Date: Sep 2004
Posts: 7
|
|
|
|
Hi, Brett
Does that BLOG hold the key to the answer I'm looking for?
RQ
|
|
09-07-04, 12:45
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
No....it's a rant about the use of XML...what's the destination for the data?
Why does it have to be in XML?
Is the destination another sql box?
|
|
09-07-04, 12:58
|
|
Registered User
|
|
Join Date: Sep 2004
Posts: 7
|
|
Oh (on the rant part)...I might use that to vent later...
No, the destination is not another SQL box.
As I mentioned earler, our information system uses SQL Server 2000. We are trying to integrate our data with another online application. The preferred data format (rather the required format) is XML. I used the SELECT...FOR XML EXPLICIT to follow their IMS specs. I got this down pat but the part where I'm trying to send the resultset to an XML file format is where I'm stuck.
|
|
09-07-04, 13:01
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
I understand that it's part of the req...but what is the destination data engine?
And show us what you've got so far....
|
|
09-07-04, 13:47
|
|
Registered User
|
|
Join Date: Sep 2004
Posts: 7
|
|
Excuse my ignorance, what is the destination data engine? As an fyi, the company that maintains/administers the inline app metioned sometime ago that they use a proprietary database system maintained via Perl.
With regards to showing what I have so far, are you tallking about the Select SQL statement or the DTS package? If it's the latter, it's just a data connection icon, a transform data task and text file destination.
|
|
09-07-04, 14:49
|
|
Registered User
|
|
Join Date: Sep 2004
Posts: 7
|
|
Hi, Brett
I just found out that company uses Berkeley flat databases for its online application.
RQ
|
|
09-07-04, 14:58
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
|
|
09-07-04, 15:08
|
|
Registered User
|
|
Join Date: Sep 2004
Posts: 7
|
|
SQL statement is below. I don't think the bcp will work on it (the Select statement), will it? How do you execute a view from a DT Spackage?
declare @NumberOfDays int
select @NumberOfDays = 21
select 1 as tag,
null as parent,
'' as [Enterprise!1],
null as [Properties!2!DataSource!element],
null as [Properties!2!DateTime!element],
null as [Group!3!!hide],
-- null as [Group!3!recstatus],
null as [Sourcedid!4!Source!element],
null as [Sourcedid!4!ID!element],
null as [Description!5!Short!element],
null as [Extension!6!Template!element]
from adterm a (nolock)
where a.code = '0001'
union
select 2 as tag,
1 as parent,
'',
'WebCT' as DataSource,
getdate() as DateTime,
null,
-- null,
null,
null,
null,
null
from adterm a (nolock)
where a.code = '0001'
union
select 3 as tag,
1 as parent,
'',
'WebCT' as DataSource,
getdate() as DateTime,
rtrim( a.code ) + cast( a.adclassschedid as char( 14 ) ) as [Group!3!!hide],
-- 1 as recstatus,
null,
null,
null,
null
from adclasssched a (nolock)
inner join adclassschedterm b (nolock)
on a.adclassschedid = b.adclassschedid
inner join adterm c (nolock)
on b.adtermid = c.adtermid
and ( getdate() + @NumberOfDays ) between c.startdate and c.enddate
and len( rtrim( c.code ) ) = 4
and c.active = 1
inner join adcourse d (nolock)
on a.adcourseid = d.adcourseid
and d.code not like 'elec%'
union
select 4 as tag,
3 as parent,
'',
'WebCT' as DataSource,
getdate() as DateTime,
rtrim( a.code ) + cast( a.adclassschedid as char( 14 ) ) as [Group!3!!hide],
-- 1 as Recstatus,
'The College of Westchester: Campus2000' as Source,
rtrim( a.code ) + '-' + rtrim( a.section ) + '-' + rtrim( c.code ) as ID,
null,
null
from adclasssched a (nolock)
inner join adclassschedterm b (nolock)
on a.adclassschedid = b.adclassschedid
inner join adterm c (nolock)
on b.adtermid = c.adtermid
and ( getdate() + @NumberOfDays ) between c.startdate and c.enddate
and len( rtrim( c.code ) ) = 4
and c.active = 1
inner join adcourse d (nolock)
on a.adcourseid = d.adcourseid
and d.code not like 'elec%'
union
select 5 as tag,
3 as parent,
'',
'WebCT' as DataSource,
getdate() as DateTime,
rtrim( a.code ) + cast( a.adclassschedid as char( 14 ) ) as [Group!3!!hide],
-- 1 as Recstatus,
'The College of Westchester: Campus2000' as Source,
rtrim( a.code ) + '-' + rtrim( a.section ) + '-' + rtrim( c.code ) as ID,
rtrim( a.descrip ) + '-' + rtrim( a.section ),
null
from adclasssched a (nolock)
inner join adclassschedterm b (nolock)
on a.adclassschedid = b.adclassschedid
inner join adterm c (nolock)
on b.adtermid = c.adtermid
and ( getdate() + @NumberOfDays ) between c.startdate and c.enddate
and len( rtrim( c.code ) ) = 4
and c.active = 1
inner join adcourse d (nolock)
on a.adcourseid = d.adcourseid
and d.code not like 'elec%'
union
select 6 as tag,
3 as parent,
'',
'WebCT' as DataSource,
getdate() as DateTime,
rtrim( a.code ) + cast( a.adclassschedid as char( 14 ) ) as [Group!3!!hide],
-- 1 as Recstatus,
'The College of Westchester: Campus2000' as Source,
rtrim( a.code ) + '-' + rtrim( a.section ) + '-' + rtrim( c.code ) as ID,
rtrim( a.descrip ) + '-' + rtrim( a.section ),
rtrim( a.code ) + '_Template'
from adclasssched a (nolock)
inner join adclassschedterm b (nolock)
on a.adclassschedid = b.adclassschedid
inner join adterm c (nolock)
on b.adtermid = c.adtermid
and ( getdate() + @NumberOfDays ) between c.startdate and c.enddate
and len( rtrim( c.code ) ) = 4
and c.active = 1
inner join adcourse d (nolock)
on a.adcourseid = d.adcourseid
and d.code not like 'elec%'
order by [Group!3!!hide],
[Sourcedid!4!ID!element],
[Description!5!Short!element],
[Extension!6!Template!element]
for xml explicit
|
|
09-10-04, 12:34
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
I give up....
Code:
USE Northwind
GO
CREATE VIEW myTable99
AS
SELECT 1 as Tag,
NULL as Parent,
Customers.CustomerID as [Customer!1!CustomerID],
NULL as [Order!2!OrderID]
FROM Customers
UNION ALL
SELECT 2,
1,
Customers.CustomerID,
Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID], [Order!2!OrderID]
FOR XML EXPLICIT
GO
CREATE TABLE myTable99(Col1 varchar(8000))
GO
INSERT INTO myTable99(Col1)
SELECT * FROM (
SELECT 1 as Tag,
NULL as Parent,
Customers.CustomerID as [Customer!1!CustomerID],
NULL as [Order!2!OrderID]
FROM Customers
UNION ALL
SELECT 2,
1,
Customers.CustomerID,
Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID], [Order!2!OrderID]
FOR XML EXPLICIT) AS XXX
GO
CREATE PROC mySproc99
AS
SELECT 1 as Tag,
NULL as Parent,
Customers.CustomerID as [Customer!1!CustomerID],
NULL as [Order!2!OrderID]
FROM Customers
UNION ALL
SELECT 2,
1,
Customers.CustomerID,
Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID], [Order!2!OrderID]
FOR XML EXPLICIT
GO
EXEC mySproc99
INSERT INTO myTable99 EXEC mySproc99
GO
DROP TABLE myTable99
DROP PROC mySproc99
GO
|
|
09-10-04, 14:26
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
|
|
09-10-04, 15:47
|
|
Registered User
|
|
Join Date: Sep 2004
Posts: 7
|
|
My sentiments -- just about -- on giving up.
Thanks for your suggestion, I'll give it a try.
The article you mentioned is informative but it said it works best on a small result set. I anticipate my result sets to vary -- with a lot leaning on pretty big result set sizes.
Many thanks for your help...
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Using FOR XML Explicit within a DTS package
