XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
mirko mehnertSubject: 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)

<root>
<row>
<METADATA_ID>1</METADATA>
<METADATA_NAME>something</METADATA>
<PRODUCTS_ID>10</PRODUCTS_ID>
<PRODUCTS_NAME>Test 10</PRODUCTS_NAME>
</row>

<row>
<METADATA_ID>1</METADATA>
<METADATA_NAME>something</METADATA>
<PRODUCTS_ID>21</PRODUCTS_ID>
<PRODUCTS_NAME>Test 21</PRODUCTS_NAME>
</row>

<row>
<METADATA_ID>1</METADATA>
<METADATA_NAME>something</METADATA>
<PRODUCTS_ID>44</PRODUCTS_ID>
<PRODUCTS_NAME>Test 44</PRODUCTS_NAME>
</row>
</root>

hope you understand this. my english is not so good.
thanks and best wishes
marcus

Postnext
Ivan PedruzziSubject: 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


Hope this helps
Ivan Pedruzzi
Stylus Studio Team

Postnext
mirko mehnertSubject: 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)

I see, I'm a bloody beginner :-)
thanks
m.

Posttop
Ivan PedruzziSubject: Access To XML Query Question
Author: Ivan Pedruzzi
Date: 31 May 2006 07:00 PM

Mirko,

Don't warry, here a place to start.

SQL/XML quick reference
http://media.datadirect.com/download/docs/connectsqlxml/csxug/sql_xml_grammar.html#wp103755

There is not much for datetime manipulation in SQL/XML.

SQL/XML does not allow to define a variable either.

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

 
Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.