Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2004
    Posts
    7

    Question Unanswered: 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?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2004
    Posts
    7
    Hi, Brett

    Does that BLOG hold the key to the answer I'm looking for?

    RQ

  4. #4
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    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.

  6. #6
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    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.

  8. #8
    Join Date
    Sep 2004
    Posts
    7
    Hi, Brett

    I just found out that company uses Berkeley flat databases for its online application.

    RQ

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No kidding...

    http://www.stanford.edu/class/cs276a...o/dbisnot.html

    Save your query as a view then dts it out...

    or better yet, use bcp....

    Where's the sql statement....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    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

  11. #11
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you didn't see this link in the blog (gratefully donated by Jay)

    Check it out

    http://www.perfectxml.com/Articles/X...rtSQLXML.asp#4
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •