xml vb script help

  • mark00189

    SSC Journeyman

    Points: 90

    VB script & Xml help

    need help with VB script & Xml...

    have 3 tbls.

    tblComp(PK CompId(int 4), compName, dateCreat(datetime 8)--no null)

    tblContact [PK contId(int 4),{fk} compId(int 4), Name, MemoCount(int 4), dateEnter(datetime 8)--no null]

    tblMemo[pk memoId (int 4), {FK} contId(int 4), {FK} compId (int 4), memo (varchar 100)]

    need to write VB Script program to retrieve comp, contact, and memo and bring them together as an XML document using the FOR XML clause of the SELECT command

    thnks

    mark..

  • aochss

    SSCommitted

    Points: 1677

    Nto sure if this is the correct direction. Take a look at the "EXPLICIT mode" topic in the "SQL Server Books On-Line". I will try to follow up with an example later in the week.

    Anton

  • aochss

    SSCommitted

    Points: 1677

    How about this?

    Anton

    -- Create Tables

    create table tblComp (CompId int, compName varchar(100), dateCreat datetime)

    create table tblContact (contId int, compId int, Name varchar(100), MemoCount int, dateEnter datetime)

    create table tblMemo (memoId int, contId int, compId int, memo varchar(100))

    -- Insert Rows

    insert into tblcomp values (1, 'Smith', getdate())

    insert into tblcomp values (2, 'Jones', getdate())

    insert into tblContact values (1, 1, 'Smith Contact1', 4, getdate())

    insert into tblContact values (2, 1, 'Smith Contact2', 4, getdate())

    insert into tblContact values (3, 2, 'Jones Contact1', 1, getdate())

    insert into tblContact values (4, 2, 'Jones Contact2', 1, getdate())

    insert into tblContact values (5, 2, 'Jones Contact3', 1, getdate())

    insert into tblContact values (6, 2, 'Jones Contact4', 1, getdate())

    insert into tblmemo values (1, 1, 1, 'Memo1 Smith COntact1')

    insert into tblmemo values (2, 1, 1, 'Memo2 Smith COntact1')

    insert into tblmemo values (3, 1, 1, 'Memo3 Smith COntact1')

    insert into tblmemo values (4, 1, 1, 'Memo4 Smith COntact1')

    insert into tblmemo values (5, 2, 1, 'Memo1 Smith COntact2')

    insert into tblmemo values (6, 2, 1, 'Memo2 Smith COntact2')

    insert into tblmemo values (7, 2, 1, 'Memo3 Smith COntact2')

    insert into tblmemo values (8, 2, 1, 'Memo4 Smith COntact2')

    insert into tblmemo values (9, 3, 2, 'Memo1 Jones COntact1')

    insert into tblmemo values (10, 4, 2, 'Memo2 Jones COntact2')

    insert into tblmemo values (11, 5, 2, 'Memo3 Jones COntact3')

    insert into tblmemo values (12, 6, 2, 'Memo4 Jones COntact4')

    -- Using Automatic Mode

    select * from tblcomp c

    inner join tblContact cn on c.compid = cn.compid

    inner join tblmemo mem on mem.contId = cn.contId

    order by c.compid, cn.contid, memoid

    FOR XML auto

    -- Returns This

    <c CompId="1" compName="Smith" dateCreat="2003-05-14T12:05:04.657"><cn contId="1" compId="1" Name="Smith Contact1" MemoCount="4" dateEnter="2003-05-14T13:26:24.110"><mem memoId="1" contId="1" compId="1" memo="Memo1 Smith COntact1"/><mem memoId="2" contId="1" compId="1" memo="Memo2 Smith COntact1"/><mem memoId="3" contId="1" compId="1" memo="Memo3 Smith COntact1"/><mem memoId="4" contId="1" compId="1" memo="Memo4 Smith COntact1"/></cn><cn contId="2" compId="1" Name="Smith Contact2" MemoCount="4" dateEnter="2003-05-14T13:26:32.313"><mem memoId="5" contId="2" compId="1" memo="Memo1 Smith COntact2"/><mem memoId="6" contId="2" compId="1" memo="Memo2 Smith COntact2"/><mem memoId="7" contId="2" compId="1" memo="Memo3 Smith COntact2"/><mem memoId="8" contId="2" compId="1" memo="Memo4 Smith COntact2"/></cn></c><c CompId="2" compName="Jones" dateCreat="2003-05-14T12:05:04.673"><cn contId="3" compId="2" Name="Jones Contact1" MemoCount="1" dateEnter="2003-05-14T13:27:01.830"><mem memoId="9" contId="3" compId="2" memo="Memo1 Jones COntact1"/></cn><cn contId="4" compId="2" Name="Jones Contact2" MemoCount="1" dateEnter="2003-05-14T13:27:01.847"><mem memoId="10" contId="4" compId="2" memo="Memo2 Jones COntact2"/></cn><cn contId="5" compId="2" Name="Jones Contact3" MemoCount="1" dateEnter="2003-05-14T13:27:01.847"><mem memoId="11" contId="5" compId="2" memo="Memo3 Jones COntact3"/></cn><cn contId="6" compId="2" Name="Jones Contact4" MemoCount="1" dateEnter="2003-05-14T13:27:01.847"><mem memoId="12" contId="6" compId="2" memo="Memo4 Jones COntact4"/></cn></c>

    -- Using Explicit Mode

    SELECT 1 as Tag,

    NULL as Parent,

    tblcomp.compID as [Company!1!CompID],

    tblcomp.compName as [Company!1!CompName],

    tblcomp.dateCreat as [Company!1!DateCreated],

    NULL as [Contact!2!ContID],

    NULL as [Contact!2!MemoCount],

    null as [Contact!2!DateCreated],

    NULL as [Memo!3!MemoID],

    NULL as [Memo!3!Memo]

    FROM tblcomp

    UNION ALL

    SELECT 2,

    1,

    tblcomp.compID,

    NULL,

    null,

    tblContact.contId,

    memoCount,

    dateEnter,

    NULL,

    NULL

    FROM tblcomp, tblContact

    WHERE tblcomp.compid = tblContact.compid

    union all

    select 3,

    2,

    tblcomp.compID,

    NULL,

    NULL,

    tblContact.contId,

    NULL,

    NULL,

    tblmemo.memoid,

    tblmemo.memo

    from tblcomp, tblContact, tblmemo

    WHERE tblcomp.compid = tblContact.compid

    and tblmemo.contId = tblContact.contId

    order by [Company!1!CompID], [Contact!2!ContID], [Memo!3!MemoID]

    FOR XML EXPLICIT

    -- Returns This

    <Company CompID="1" CompName="Smith" DateCreated="2003-05-14T12:05:04.657"><Contact ContID="1" MemoCount="4" DateCreated="2003-05-14T13:26:24.110"><Memo MemoID="1" Memo="Memo1 Smith COntact1"/><Memo MemoID="2" Memo="Memo2 Smith COntact1"/><Memo MemoID="3" Memo="Memo3 Smith COntact1"/><Memo MemoID="4" Memo="Memo4 Smith COntact1"/></Contact><Contact ContID="2" MemoCount="4" DateCreated="2003-05-14T13:26:32.313"><Memo MemoID="5" Memo="Memo1 Smith COntact2"/><Memo MemoID="6" Memo="Memo2 Smith COntact2"/><Memo MemoID="7" Memo="Memo3 Smith COntact2"/><Memo MemoID="8" Memo="Memo4 Smith COntact2"/></Contact></Company><Company CompID="2" CompName="Jones" DateCreated="2003-05-14T12:05:04.673"><Contact ContID="3" MemoCount="1" DateCreated="2003-05-14T13:27:01.830"><Memo MemoID="9" Memo="Memo1 Jones COntact1"/></Contact><Contact ContID="4" MemoCount="1" DateCreated="2003-05-14T13:27:01.847"><Memo MemoID="10" Memo="Memo2 Jones COntact2"/></Contact><Contact ContID="5" MemoCount="1" DateCreated="2003-05-14T13:27:01.847"><Memo MemoID="11" Memo="Memo3 Jones COntact3"/></Contact><Contact ContID="6" MemoCount="1" DateCreated="2003-05-14T13:27:01.847"><Memo MemoID="12" Memo="Memo4 Jones COntact4"/></Contact></Company>

    -- I Added Line Feeds and Indents

    <Company CompID="1" CompName="Smith" DateCreated="2003-05-14T12:05:04.657">

    <Contact ContID="1" MemoCount="4" DateCreated="2003-05-14T13:26:24.110">

    <Memo MemoID="1" Memo="Memo1 Smith COntact1"/>

    <Memo MemoID="2" Memo="Memo2 Smith COntact1"/>

    <Memo MemoID="3" Memo="Memo3 Smith COntact1"/>

    <Memo MemoID="4" Memo="Memo4 Smith COntact1"/>

    </Contact>

    <Contact ContID="2" MemoCount="4" DateCreated="2003-05-14T13:26:32.313">

    <Memo MemoID="5" Memo="Memo1 Smith COntact2"/>

    <Memo MemoID="6" Memo="Memo2 Smith COntact2"/>

    <Memo MemoID="7" Memo="Memo3 Smith COntact2"/>

    <Memo MemoID="8" Memo="Memo4 Smith COntact2"/>

    </Contact>

    </Company>

    <Company CompID="2" CompName="Jones" DateCreated="2003-05-14T12:05:04.673">

    <Contact ContID="3" MemoCount="1" DateCreated="2003-05-14T13:27:01.830">

    <Memo MemoID="9" Memo="Memo1 Jones COntact1"/>

    </Contact>

    <Contact ContID="4" MemoCount="1" DateCreated="2003-05-14T13:27:01.847">

    <Memo MemoID="10" Memo="Memo2 Jones COntact2"/>

    </Contact>

    <Contact ContID="5" MemoCount="1" DateCreated="2003-05-14T13:27:01.847">

    <Memo MemoID="11" Memo="Memo3 Jones COntact3"/>

    </Contact>

    <Contact ContID="6" MemoCount="1" DateCreated="2003-05-14T13:27:01.847">

    <Memo MemoID="12" Memo="Memo4 Jones COntact4"/>

    </Contact>

    </Company>

  • Melville

    SSCommitted

    Points: 1985

    Create an xml document (msxml2.domdocument) and use it to accept the output from the for xml t-sql.

    TIP: "for xml auto, elements" is the easiest way to use the command for beginners.

    set cn=server.CreateObject("ADODB.Connection")

    set cmd=server.CreateObject("ADODB.Command")

    cn.Open strConn

    Set objXMLDoc = Server.CreateObject("Msxml2.DOMDocument")

    strSQL=<whatever>

    strQuery="<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'><results><sql:query>" + strSQL + "</sql:query></results></root>"

    set cmd.ActiveConnection=cn

    cmd.CommandText=strQuery

    cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

    cmd.Properties("Output Stream")=objXMLDoc

    cmd.Execute,,1024

    set cmd=nothing

    set cn=nothing

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply