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
Wagner ReinhardSubject: SQL/XML Error
Author: Wagner Reinhard
Date: 08 Jun 2005 12:57 PM
Originally Posted: 08 Jun 2005 12:19 PM
Hello. How can I rename the root element? My query should produce such a document without the root element: How can I handle this with sql/xml? thanks in advance reinhard <mondial> ... </mondial>

Postnext
Ivan PedruzziSubject: SQL/XML Error
Author: Ivan Pedruzzi
Date: 08 Jun 2005 03:15 PM
Hi Wagner,

By design the "DB to XML" query result is always a well formed document so it always have one and only one root.

If you need to remove the enclosing element you could use XQuery to post process the result to generate a sequence of elements.

For example the following XQuery uses a JDBC URL to get the default SQL/XML view and extracts row elements

doc("db:///jdbc:datadirect:sqlserver://localhost:1433;schema=dbo;table=employee;user=sa;DatabaseName=pubs;xmlforest=false;urltype=.xml")/*/row


The XQuery result looks like this

<row>
...
</row>
<row>
...
</row>



Hope this helps
Ivan Pedruzzi
Stylus Studio Team

Postnext
Wagner ReinhardSubject: SQL/XML Error
Author: Wagner Reinhard
Date: 09 Jun 2005 04:28 AM
ok thank you.

I have another problem : When I execute above "SQL/XML" I always get an error like this: "Nested query 1 should select at least one column."
I should fulfill the DTD:

DOCTYPE Mondial[
<!Element Mondial(Kontinent+,Land+)>
<!Element Kontinent(LName,Prozent)+>
<!Element Land(LName,Stadt*)>
<!Element Stadt(Sname,Einwohner)>
<!Element SName(#PCDATA)>
<!Element Einwohner (#PCDATA>
<!Element LName(#PCDATA>
<!Element Prozent(#PCDATA>
<!ATTLIST Kontinent KName #REQUIRED>
]

The tables are
LAND : LCode,LName,HStadt,Fläche
Stadt: SName,PName,LCode,Einwohner
Lage : LCode,Kontinent,Prozent

It does not work and I dont know why ? Please help me !

Reinhard

SELECT XMLElement
(
NAME Mondial,
(
SELECT XMLElement
(
NAME Kontinent,
XMLAttributes (La.Kontinent AS KName),
XMLElement (NAME LName, L1.LName),
XMLElement (NAME Prozent, La.Prozent)
)
FROM My.dbo.Lage La
),
(
SELECT XMLElement
(
NAME Land,
XMLElement(NAME LName, L2.LName),
XMLAGG
( XMLELEMENT
( NAME Stadt,
XMLElement (NAME SName, Stadt.SName),
XMLElement (NAME Einwohner,Stadt.Einwohner)
)
ORDER BY Stadt.Einwohner DESC
)
)

FROM My.dbo.Land L2, My.dbo.Stadt Stadt
WHERE L1.LCode = L2.LCode AND Stadt.LCode = L2.LCode
GROUP BY L2.LName
)
) AS Mondial
FROM My.dbo.Land L1

Posttop
Ivan PedruzziSubject: SQL/XML Error
Author: Ivan Pedruzzi
Date: 23 Jun 2005 11:00 PM
Wagner,

Use the following as example to design a nested query
The query correlates books with authors in a many to many relation

SELECT
XMLELEMENT(name "book",
XMLELEMENT(name "title",t.title),
XMLELEMENT(name "authors",
(
SELECT XMLELEMENT(name "author", a.au_fname)
FROM pubs.dbo.authors a, pubs.dbo.titleauthor ta
WHERE t.title_id = ta.title_id AND ta.au_id = a.au_id
)
)
)
FROM pubs.dbo.titles t

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.