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
Steve TrythallSubject: sql/xml returns invalid results
Author: Steve Trythall
Date: 05 Jul 2005 02:49 AM

I have problems with sql/xml returning results that are not parsed by SS as valid results. I am using Oracle 9i and SS 6 Professional build 287m.

Certain actions generate the correct db results but wrap them with:

&lt;element-name&gt; instead of <element-name> which SS treats as text instead of XML.

For example:

SELECT
XMLELEMENT(name "row",
XMLELEMENT(name "ID",t.ID),
XMLELEMENT(name "TYPE",t.TYPE)
)
FROM SSED.SSED_EQUIPMENT t

will produce:

<?xml version="1.0" encoding="UTF-8" ?>
<root>
<row>
<Column-1>&lt;row&gt;&lt;ID&gt;18711&lt;/ID&gt;&lt;TYPE&gt;LRU&lt;/TYPE&gt;&lt;/row&gt;</Column-1>
</row>
</root>

If I remove "XMLELEMENT(name "TYPE",t.TYPE)" this will work correctly and produce:

<?xml version='1.0' encoding='UTF-8'?>
<root>
<row><ID>18711</ID><VERSION>0</VERSION></row>
</root>




Postnext
Steve TrythallSubject: sql/xml returns invalid results
Author: Steve Trythall
Date: 05 Jul 2005 02:57 AM

Here is another example where it goes wrong. I have a query with a subquery to get devices and their pins. I have stripped the query down to make to its core:

select
xmlelement (name device,
XMLATTRIBUTES(e.ID),
XMLELEMENT( NAME "pins",
(select XMLAGG(XMLELEMENT( NAME "pin",
XMLATTRIBUTES(t.label),
))
from SSED_TERMINATION t
)))

from SSED_EQUIPMENT e

If I view the results in the web browser mode in preview I get the results I expect except the device element is in a black font (indicating that it is text and not xml). In the text view it looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<root>
<row>
<Column-1>&lt;DEVICE ID=&quot;18711&quot;&gt;&lt;pins&gt;&lt;pin LABEL=&quot;A&quot;&gt;&lt;/pin&gt;&lt;pin LABEL=&quot;B&quot;&gt;&lt;/pin&gt;&lt;/pins&gt;&lt;/DEVICE&gt;</Column-1>
</row>
</root>




Postnext
Ivan PedruzziSubject: sql/xml returns invalid results
Author: Ivan Pedruzzi
Date: 05 Jul 2005 04:59 PM
Hi Steve,

I am having hard time to reproduce the same behavior.
By any chance one of the columns you are working with contains XML or is defined as XMLType?

If that is case, Coould you try with the latest Stylus Studio version (6.3)?


Thank You
Ivan Pedruzzi
Stylus Studio



>
>Here is another example where
>it goes wrong. I have a query
>with a subquery to get devices
>and their pins. I have
>stripped the query down to
>make to its core:
>
>select
> xmlelement (name device,
> XMLATTRIBUTES(e.ID),
> XMLELEMENT( NAME "pins",
>(select XMLAGG(XMLELEMENT(
>NAME "pin",
> XMLATTRIBUTES(t.label),
> ))
> from SSED_TERMINATION t
> )))
>
>from SSED_EQUIPMENT e
>
>If I view the results in the
>web browser mode in preview I
>get the results I expect
>except the device element is
>in a black font (indicating
>that it is text and not xml).
>In the text view it looks like
>this:
>
><?xml version="1.0"
>encoding="UTF-8" ?>
><root>
> <row>
><Column-1>&lt;DEVICE
>ID=&quot;18711&quot;&a
>mp;gt;&lt;pins&gt;&
>;lt;pin
>LABEL=&quot;A&quot;&am
>p;gt;&lt;/pin&gt;&
>lt;pin
>LABEL=&quot;B&quot;&am
>p;gt;&lt;/pin&gt;&
>lt;/pins&gt;&lt;/DEVIC
>E&gt;</Column-1>
> </row>
></root>
>
>
>
>

Posttop
Ivan PedruzziSubject: sql/xml returns invalid results
Author: Ivan Pedruzzi
Date: 05 Jul 2005 10:38 AM
Steve

The query should throw an error because "type" is SQL keyword.
You can escape keywords with quotes

SELECT
XMLELEMENT(name "row",
XMLELEMENT(name "ID",t.ID),
XMLELEMENT(name "TYPE",t."TYPE")
)
FROM SSED.SSED_EQUIPMENT 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.