Subject:Access To XML Query Question Author:mirko mehnert Date:30 May 2006 08:03 AM
Hello,
I'm new to complex XML Applications and I've got a question, with hope, someone can give me a hint.
I've got a Database, with different tables.
let's say, there is a Table, with global METADATA and a table with PRODUCTS. METADATA contains some different Suppliers and PRODUCTS ... products.
The target XML should be like this:
<root>
<header>all METADATA from one ID</header>
<products>
<METADATA_ID>all Products, which have the called METADATA_ID</METADATA_ID>
<PRODUCTDATA />
</products>
</root>
that means, I need to build a DB2XML Query, which build a XML with all Databaseentries, which fit my request (over The METADATA_ID)
but how I handle this?
The METADATA contains one Dataset
PRODUCTS contains much more.
Should I do 2 Querys/DB2XML Files and join them in the final XML or should I write the same METADATA in each <row></row>
set of the DB2XML result like this:
(and query them again in the final XML to extract the METADATA in the header and the PRODUCTS into the cataloge part)
Subject:Access To XML Query Question Author:Ivan Pedruzzi Date:30 May 2006 12:11 PM
Mirko,
I guess you are thinking to use SQL/XML. You need to use a query with an inner query.
Take a look to the following example based on the Microsoft Access database example Northwind
SELECT
XMLELEMENT(name "Category",
XMLELEMENT(name "Name",c.CategoryName),
XMLELEMENT(name "Products",
(
SELECT
XMLELEMENT(name "ProductName",p.ProductName)
FROM Northwind.dbo.Products p
WHERE p.CategoryID = c.CategoryID
)
)
)
FROM Northwind.dbo.Categories c
Subject:Access To XML Query Question Author:mirko mehnert Date:31 May 2006 06:54 AM
thank you for the hint.
that makes some more clearer.
The next big thing I stumble above, is which SQL Function are usable?
There is CONCAT() I can't use it only with {fn CONCAT(string,string2)}
or I use XMLCONCAT ok, but what about Date Formating Functions?
MONTH(now()) is working, but what when I need to extract "MAY 2006" from NOW() ?
XMLELEMENT(name "DATE",DATE(now()))
should output <DATE>2006-05-31</DATE>
But it doesn't... :-(
Is there a clear Reference for SQL/XML? I think the SQL is different for ODBC Connection, or MySQL and so on.
is it possible, to define global variables in the SQL Editor? (else, I need to Hardcode some XML Values)