xml vb script help

Question

xml vb script help

mark00189

SSC Enthusiast

Points: 140
More actions
May 13, 2003 at 2:26 pm

#357767

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

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

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

aochss SSCommitted Points: 1677 More actions · Answer 1

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 More actions · Answer 2

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

-- 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,

tblContact.contId,

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

-- I Added Line Feeds and Indents

</Contact>

</Contact>

</Company>

</Contact>

</Contact>

</Contact>

</Contact>

</Company>

Melville SSCommitted Points: 1985 More actions · Answer 3

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