XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
search
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Home » Boards » Stylus Studio Developer Network » Stylus Studio Technical Forum » SQLXML and Subquery
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
- Stylus Studio Technical Forum (14621)
-> - More flexible generated Java c... (1)
-> + Generic Schemas for representi... (2)
-> + Stylus Studio Professional rep... (2)
-> + XML XML Help (2)
-> + xsd help (2)
-> + XML data element friendly name... (2)
-> + HTML Tidy adapter is missing? (3)
-> + New Guy Here... (2)
-> + How to combine two similar fil... (2)
-> + Berkeley XML DB (4)
-> + What is the context node in th... (3)
-> + Multi element List (4)
-> + Cannot preview XSL transformat... (9)
-> + Why is this variable not showi... (7)
-> + Customising Schema Prefix (3)
-> + Please help!! XML AND XSL Tra... (6)
-> + XML to Excel in Home Edition (2)
-> + how to copy elements from an x... (2)
-> + Setting Saxon parameters in pi... (2)
-> + Trying to move SS license (3)
-> + Does SS PRO Support CSS (2)
-> + Selected values are not showin... (3)
-> + Copy Xpath To Clipboard Option... (2)
-> + Upgrading to SS 2006 3 - Activ... (3)
-> + XSL Efficiency tips (4)
-> - Report page Size and table hea... (1)
-> + Endless loop with xquery (8)
-> - Xpress XML (1)
-> + Extracting XML data into SQL s... (2)
-> + Crashes when I save (2)
-> + XSL cannot save: I/O error...b... (8)
-> + Diagram view has disappeared f... (5)
-> + Convert from http://www.w3.org... (2)
-> + Round-trip Converting; CSV-XML... (2)
-> + XSL error function call PHP (8)
-> + WYSIWYG XSLT Designer (5)
-> + generate xhtml+css from xml re... (2)
-> + generate xhtml+css from xml re... (3)
-> + generate xhtml+css from xml re... (2)
-> - Help using xsd xs:redefine nee... (1)
-> + Data loss opening External sch... (2)
-> + Install Apache FOP Plugin with... (8)
-> + Generating a Publisher Canvas ... (2)
-> + Stylus Studio comment block no... (3)
-> + Report Designer Screen Repaint... (5)
-> + validating on values (5)
-> + reuse of xslt (2)
-> + The evaluation period for the ... (2)
-> + export to documentation (3)
-> + Validating XML files using sev... (2)
-> + Missing 'Command' Tab in Custo... (3)
-> + Do I have to download new vers... (6)
-> + Can SS validate tags used in a... (5)
-> + validating schemas (2)
-> + Off-Topic Question - Looking f... (4)
-> + cannot see anything in mapper ... (2)
-> + Validating XSD schema sets (7)
-> + Standard Validation Engines (3)
-> + Style Sheet amendment to inclu... (2)
-> + Documentation - prevent import... (3)
-> + Debugging breakpoints with Sax... (17)
-> + Conversion (3)
-> + New and I am sure this is an e... (7)
-> + Saxon 8 with multiple xml inpu... (3)
-> + Compilation error (5)
-> + webservice call failing - pls ... (3)
-> + C# from Java Binding Classes (3)
-> + Java binding class generation (5)
-> + Validation of deeply-nested sc... (5)
-> + How to make Drag and drop in X... (2)
-> + Malformed XPATH expression par... (5)
-> + Toolbars and missing options (4)
-> + Converting Text File (2)
-> + Java Heap Issue (2)
-> + Formatting SOAP messages (6)
-> + Attribute Entity Expansion on ... (2)
-> + Text File to xml conversion wi... (9)
-> + Different XPath results when u... (7)
-> + Where is my XSLT mapper module (3)
-> + Where is my XSLT WYSIWYG Edito... (2)
-> + Regarding Stylus support of IA... (2)
-> + Debugging Problem with Enterpr... (5)
-> + various report designer prob;e... (12)
-> - will it EVER get fixed ?? Stru... (1)
-> - Migrate from wysiwyg xslt edit... (1)
-> + Entity Resolver in UI? (4)
-> + Visual Studio and Stylus Studi... (2)
-> + how to get rid of parameter in... (8)
-> + XQuery file (2)
-> + 505 error calling a WS from SS... (2)
-> + Configure Subversion with Styl... (2)
-> + XSLT Mapping of xsi:nil and xs... (2)
-> + Printing values in a table or ... (2)
-> + query on using stylus API for ... (12)
-> + WSDL retruns no response (2)
-> + SS XSL Processor Bug: Count() (6)
-> + Stylus Studio crashes after re... (2)
-> + Custom Converter in IBM JVM is... (10)
-> + Malformed XPATH epressions on ... (4)
-> - XML Report table borders when ... (1)
-- Previous [1501-1520] [1521-1540] [1541-1560] Next
+ Website Feedback (249)
+ XSLT Help and Discussion (7625)
+ XQuery Help and Discussion (2017)
+ Stylus Studio FAQs (159)
+ Stylus Studio Code Samples & Utilities (364)
+ Stylus Studio Announcements (113)
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
   
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.