SSDN - Access To XML Query Question

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Conferences

	Stylus Studio Feature Requests (1192)
	Stylus Studio Technical Forum (14621)
	Website Feedback (249)
	XSLT Help and Discussion (7625)
	XQuery Help and Discussion (2017)
	Issue with Processing Instruct... (2)
	problem converting json to XML... (2)
	Problem base64 decoding string... (3)
	Problems posting multipart for... (5)
	trouble with download of price... (2)
	Problem with http-post not bei... (3)
	path problem, xps_file:writeAl... (9)
	Xquery update support? (2)
	problem with Stylus studio try... (5)
	adding dtd reference to xml ou... (4)
	xquery escaping ambarsand when... (3)
	Whitespace problem when return... (5)
	Problem with namespace prefix ... (5)
	Sending via SFTP returns unexp... (1)
	Query and Sftp clent (4)
	xquery and try - catch (3)
	Query + ddtek:http-post optio... (5)
	Example files referenced in do... (3)
	Automatic Error Detection and ... (3)
	Working with result of ddtek:h... (2)
	[1-20] [21-40] [41-60] Next
	Stylus Studio FAQs (159)
	Stylus Studio Code Samples & Utilities (364)
	Stylus Studio Announcements (113)

Topic

mirko mehnert

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)

<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

Ivan Pedruzzi

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

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

mirko mehnert

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)

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

Ivan Pedruzzi

Subject: 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

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 to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

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.