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
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Jasbir LutheraSubject: SQLXML and Subquery
Author: Jasbir Luthera
Date: 04 Apr 2007 01:50 PM
I am trying to do a subquery (which returns exactly one row and one column) to get the value of an attribute, it doesn't work - no error messages:

SELECT XMLELEMENT(name "site",
XMLELEMENT(name "db_id", XMLATTRIBUTES('1-1-1' AS "attr"), t.id),
XMLELEMENT(name "name",
XMLATTRIBUTES((SELECT c.site_id
FROM site c
WHERE c.status = 'active'
AND c.location = 'us'
AND c.state = 'ca') AS "site_id"), t.site_name)
)
FROM plants p

---------------
Output:

<root>
<site>
<db_id attr='1-1-1'>5</db_id >
<site_name>PTP</site_name>
</site>
</root>

PROBLEM: the site_name element should have an attribute called site_id from the respective subquery. See below for subquery results.
-----------------
For subquery:

SELECT c.site_id
FROM site c
WHERE c.status = 'active'
AND c.location = 'us'
AND c.state = 'ca'

Output:
<root>
<row>
<site_id>XYZ</site_id>
</row>
</root>

Posttop
Ivan PedruzziSubject: SQLXML and Subquery
Author: Ivan Pedruzzi
Date: 04 Apr 2007 02:27 PM
Jasbir

If you are quering either SQL Server, Oracle, DB2, Sybase you should consider using XQuery.

declare variable $site_id := collection('site')/site[
status = 'active' and location = 'us' and state = 'ca']/site_id;

for $plant in collection("plants")
return
<site>
<db_id attr="">{$plan/id}</db_id>
<name site_id="{$site_id}">{$plan/site_name}</name>
</site>


A SQL/XML sub query cannot use XMLATTRIBUTES as root
Here an example of sub query

SELECT
XMLELEMENT(name "row",
XMLELEMENT(name "lname",
(SELECT XMLELEMENT(name "desc", j.job_desc)
FROM pubs.dbo.jobs j
WHERE j.job_id = 1)
,t.lname)
)
FROM pubs.dbo.employee t

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

 
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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.