Wanted: Simple query->DOM example

I'm not sure what you mean by "populates a DOM", as to me, DOM is an API with
which you can manipulate XML. If you post some DDL, sample data and expected
results, maybe we can help a bit more.

I think you might mean an xsd, so I've scripted out a simple example for SQL
2005. "Language neutral" would be very difficult as SQL 2005 offers great
XML support, with FOR XML and OPENXML and the xml datatype. I don't think
Access offers any of these, at least not in the same way, so it would be
difficult to reproduce the results below without writing custom VBA code.

-- SQL 2005 example, prints xsd and XML
SET NOCOUNT ON

DROP TABLE #users
GO
CREATE TABLE #users ( user_id INT PRIMARY KEY, user_name VARCHAR(10),
date_added DATETIME DEFAULT(GETDATE()) )
GO
DROP TABLE #skills
GO
CREATE TABLE #skills ( skill_id INT PRIMARY KEY, skill_name VARCHAR(20),
date_added DATETIME DEFAULT(GETDATE()) )
GO
DROP TABLE #user_skills
GO
CREATE TABLE #user_skills ( user_skill_id INT IDENTITY UNIQUE, user_id INT,
skill_id INT, date_added DATETIME DEFAULT(GETDATE()), PRIMARY KEY ( user_id,
skill_id ) )
GO

INSERT INTO #users ( user_id, user_name ) VALUES ( 1, 'Siegried' )
INSERT INTO #users ( user_id, user_name ) VALUES ( 2, 'wBob' )

INSERT INTO #skills ( skill_id, skill_name ) VALUES ( 100, 'Access' )
INSERT INTO #skills ( skill_id, skill_name ) VALUES ( 101, 'SQL' )
INSERT INTO #skills ( skill_id, skill_name ) VALUES ( 102, 'XML' )
INSERT INTO #skills ( skill_id, skill_name ) VALUES ( 103, 'DOM' )

INSERT INTO #user_skills ( user_id, skill_id ) VALUES ( 1, 100 )
INSERT INTO #user_skills ( user_id, skill_id ) VALUES ( 2, 101 )
INSERT INTO #user_skills ( user_id, skill_id ) VALUES ( 2, 102 )
GO

SELECT u.user_id, u.user_name, s.skill_id, s.skill_name
FROM #user_skills us
INNER JOIN #users u ON us.user_id = u.user_id
INNER JOIN #skills s ON us.skill_id = s.skill_id

-- Auto
SELECT u.user_id, u.user_name, s.skill_id, s.skill_name
FROM #user_skills us
INNER JOIN #users u ON us.user_id = u.user_id
INNER JOIN #skills s ON us.skill_id = s.skill_id
FOR XML AUTO, XMLSCHEMA
-- Other sample options
--FOR XML RAW, XMLSCHEMA
--FOR XML RAW, ELEMENTS, XMLSCHEMA

HTH
wBob
"Siegfried Heintze" wrote:

> Can someone point me to database neutral (i.e., works with MSAccess and SQL
> Server) example that populates a DOM from a SQL SELECT statement?
>
> Thanks,
> Siegfried