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 » Query DB and convert output to pre-defined XML format
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
- Stylus Studio Technical Forum (14621)
-> + Maximum recursion depth exceed... (3)
-> + How do I develop the server si... (2)
-> + XSD Reuse (2)
-> + what happened to my preview re... (3)
-> + How do I use the Custom XML Co... (4)
-> + WYSIWYG missing in release 2 o... (2)
-> + space between elements (3)
-> + Oracle XML Gateway output CONV... (6)
-> + Someone please help me install... (5)
-> + XML Schema (XSD) to COBOL Data... (2)
-> + Can't compile Java (9)
-> + Trouble using the UDDI Browser (5)
-> + JVM Problem when installing Ar... (2)
-> + Stylus Studio Enterprise 8r2 d... (2)
-> - xml report headers (1)
-> + ddtek:sql-delete and ddtek:sql... (4)
-> + Validation blocked (4)
-> + List from XMl to be displayed ... (2)
-> + reports - changing data source (5)
-> + Disable HTML tags in Sense:X (8)
-> + .NETCompiledTransform Paramete... (3)
-> + Why Is The Saxon Processor So ... (13)
-> + Still can not get "integrated ... (15)
-> + quick xml gen from xsd (2)
-> - new to sylus, issue with if va... (1)
-> + Question about using Data Dire... (5)
-> + Why is the DB to XML Data Sour... (2)
-> + Relational db => XML pipeline (2)
-> + connection to oracle 10g (8)
-> + xsl:function misdemeanor (3)
-> + Records Extraction from XML (2)
-> + line break variable destroyed (2)
-> + Restricting on an extended typ... (5)
-> + folding all xml elements at on... (2)
-> + help converting a text file to... (5)
-> + Java Functions (5)
-> + XML Schema - extend then restr... (6)
-> + Converting SGML to XML in pipe... (2)
-> + Unattended install? (3)
-> + what are the differences betwe... (2)
-> + cannot install (2)
-> + Missing Parameters within Web ... (3)
-> + XSLT processors not creating o... (8)
-> - How to format a seperate colum... (1)
-> + Finnish language characters (3)
-> + x12 schemas (10)
-> + Howto search multiple XML file... (3)
-> + Chameleon Schema? (2)
-> + Problems generating pdf (9)
-> + Cannot install Stylus Studio R... (19)
-> + source xml of a report (3)
-> + File I/O Error when encounter ... (3)
-> + substitution groups in xslt ma... (4)
-> + Delete rows in a MS SQL Server... (7)
-> - Attaching DB2 Database in File... (1)
-> + Can't debug xsl examples with ... (14)
-> + Best way to transform existing... (2)
-> + Exception in generated java co... (3)
-> + Java API for converting EDI X1... (2)
-> + XPath Query Editor slow perfor... (7)
-> + mapper schema problem (5)
-> + cursor issue in stylus studio ... (2)
-> + how to generate xslt code for ... (4)
-> + Web Service (12)
-> + Additional rule (2)
-> + Help needed: XMLSchema and unp... (9)
-> + Module settings for external X... (3)
-> + Finding Errors in the XML File (6)
-> + CSV 2 XML (2)
-> + Automatic Namespace when using... (2)
-> + Can't find 'Associate XML Sche... (3)
-> + Calling Stylus Studio API from... (2)
-> + EDI TO XML using the Stylus St... (4)
-> + MarkLogic Integration (4)
-> + (null)0:0 (3)
-> + Recent Windows Patch for MSXML... (3)
-> + Show XML from Schema (2)
-> + xml publisher - report to xslt... (4)
-> + EDI TO XML using the Stylus St... (3)
-> + Unable to Generate XML file fr... (6)
-> + DB to XML in Enterprise Suite (2)
-> + pdf to xml (2)
-> - Open External Schema - button ... (1)
-> - Unable to Generate XML from xC... (1)
-> + Unable to Generate XML from xC... (2)
-> + pre-proc processing in xml (2)
-> + FATAL ERROR: Expected comment ... (2)
-> + How to split window (3)
-> + XML Transformation Architectur... (2)
-> + only one record get run in an ... (2)
-> + xbrl schema file (2)
-> + Compare of 2 xml files (2)
-> + config control (4)
-> + create copy link in mapper (5)
-> + SGML DTD (3)
-> + command line access to your xs... (4)
-> + XML Schema: Attribute Choice (2)
-> + Upload file w/ topic (6)
-> - Can't View Properties (1)
-> + Can't View Properties (5)
-- Previous [1381-1400] [1401-1420] [1421-1440] 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
Edward BartholomewSubject: Query DB and convert output to pre-defined XML format
Author: Edward Bartholomew
Date: 16 May 2007 03:30 PM
I'd like to query my sql server database and then transform the output into an xml document. The xml document that I need to create needs to be the same as one that exists. I need to insert my data into a document that has a particular structure that can be read by the system into which the data needs to flow. I'm new to this conversion process and hope to achieve it with a combination of sql and stylus studio. Here is some sample data and I'm attaching the xml document that has the structure into which I need to pass the data.


UnknownCLIN_DEF_DB.xml


UnknownCMIndicatorDefinitions.xml
Postnext
Minollo I.Subject: Query DB and convert output to pre-defined XML format
Author: Minollo I.
Date: 16 May 2007 04:31 PM
What you are describing is easily doable working with different databases supported by DataDirect XQuery (http://www.xquery.com). Unfortunately MySQL is not supported (yet), and you won't be able to manipulate data inside MySQL as XML using the current version.

If you can wait a few more months, things will change...

Postnext
Edward BartholomewSubject: Query DB and convert output to pre-defined XML format
Author: Edward Bartholomew
Date: 22 May 2007 04:56 PM
Thanks - I downloaded and installed DataDirect XQuery. I can open a database table in Stylus Studio, but only with the default xml. I was able to open my destination xml file format using XML converter. I've looked through the documentation but I'm having trouble connecting the dots. Any stepwise advice for going from a database to the converted file? I'll attach my xml converted file.


UnknownCM_Conv_1.conv
xml conversion destination format
Postnext
Edward BartholomewSubject: Query DB and convert output to pre-defined XML format
Author: Edward Bartholomew
Date: 23 May 2007 12:11 PM
Here's my thought about this question: This can't be rocket science - if your company would like us to consider your tool, I think we should expect to have someone walk us through what should be a pretty simple conversion. Thanks.
Postnext
Minollo I.Subject: Query DB and convert output to pre-defined XML format
Author: Minollo I.
Date: 23 May 2007 12:57 PM
Edward,
you don't need to use the "Custom XML Conversion" tool to do what you are trying to achieve; that tool is meant to map non-XML documents into XML.

Relational data is seen and manipulated "as if it was" XML by DataDirect XQuery; and that's what you should use. I think this video will help:
http://www.stylusstudio.com/videos/ddxqdemo/datadirectxquery.html

Postnext
Edward BartholomewSubject: Query DB and convert output to pre-defined XML format
Author: Edward Bartholomew
Date: 25 May 2007 04:05 PM
Originally Posted: 25 May 2007 04:04 PM
This advice helped considerably! I'm at a point where I can query my database and output the data in the xml format required. What I still need to do is to precede that data with the following text. Can you tell me the steps to take? This will make my document compatible with the destination system. Thanks.

<?xml version="1.0" encoding="utf-8"?>
<dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
<metadata>
<item name="INDICATOR_KEY" type="xs:int"/>
<item name="AGENCY_GROUP_KEY" type="xs:int"/>
<item name="Agency" type="xs:string" length="20"/>
<item name="Indicator" type="xs:string" length="100"/>
<item name="IsDocument" type="xs:int"/>
<item name="AgencyDocument" type="xs:string" length="200"/>
<item name="DocumentKey" type="xs:string" length="50"/>
<item name="DocumentValue" type="xs:string" length="2000"/>
<item name="AgencyHref" type="xs:string" length="200"/>
<item name="LinkHref" type="xs:string" length="100"/>
<item name="LinkPage" type="xs:string" length="4"/>
</metadata>
Postnext
Ivan PedruzziSubject: Query DB and convert output to pre-defined XML format
Author: Ivan Pedruzzi
Date: 25 May 2007 06:54 PM

Hi Edward

you can almost copy verbatim the header in the query.
If you send us (stylus-field-report @ progress.com) the files involved we will show you how.

Hope this helps
Ivan Pedruzzi
Stylus Studio Team
Postnext
Edward BartholomewSubject: Query DB and convert output to pre-defined XML format
Author: Edward Bartholomew
Date: 29 May 2007 11:43 AM
I'm attaching the document that formats the data from my database query. So, my goal is to create a document that has the xml declaration and the xmlns text (from my last post) followed by the data in the format in this attached file. Thanks!


UnknownXQUERY_DATA.xquery
Postnext
(Deleted User) Subject: Query DB and convert output to pre-defined XML format
Author: (Deleted User)
Date: 31 May 2007 08:31 AM
Hi Edward,
you can just add this text to your query

<dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
<metadata>
<item name="INDICATOR_KEY" type="xs:int"/>
<item name="AGENCY_GROUP_KEY" type="xs:int"/>
<item name="Agency" type="xs:string" length="20"/>
<item name="Indicator" type="xs:string" length="100"/>
<item name="IsDocument" type="xs:int"/>
<item name="AgencyDocument" type="xs:string" length="200"/>
<item name="DocumentKey" type="xs:string" length="50"/>
<item name="DocumentValue" type="xs:string" length="2000"/>
<item name="AgencyHref" type="xs:string" length="200"/>
<item name="LinkHref" type="xs:string" length="100"/>
<item name="LinkPage" type="xs:string" length="4"/>
</metadata>
... here insert the <DATA>...</DATA> from your existing query
</dataset>

As for the <?xml header, you cannot add it to query, as it should be added by the processor itself (how you do this depends on the processor used). If you use DDXQ, you should add this declaration at the beginning of the query:

declare option ddtek:serialize "omit-xml-declaration=no";


Hope this helps,
Alberto

Postnext
Edward BartholomewSubject: Query DB and convert output to pre-defined XML format
Author: Edward Bartholomew
Date: 31 May 2007 11:45 AM
Ok, I'm a step closer...I can paste everything in and a document containing the metadata and the data is generated. The only issue is that I had to remove the xmlns reference to cognos to get it to compile. So, I had to remove

<dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">

and replace it with <dataset xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">


The error message returned when I include the xmlns is:


com.ddtek.xquery3.XQQueryException: [DataDirect][XQuery][err:XPST0005]Error at line 19, column 62. Static error. The static type of step 'child::MEASURE_2' is empty-sequence(). (Did you mean 'MEASURE_2'?)

at com.ddtek.xquery3.xqj.Util.XQQueryException(Util.java:376)

at com.ddtek.xquery3.xqj.Util.XQException(Util.java:250)

at com.ddtek.xquery3.xqj.layer.DDXQAbstractExpression.createOrGetExecutionContext(DDXQAbstractExpression.java:606)

at com.ddtek.xquery3.xqj.layer.DDXQExpression._executeQuery(DDXQExpression.java:99)

at com.ddtek.xquery3.xqj.layer.DDXQExpression.executeQuery(DDXQExpression.java:117)

Caused by: com.ddtek.xquery.typing.TypeVisitor$TypeVisitorException: [XPST000502][DataDirect][XQuery][err:XPST0005]Static error. The static type of step 'child::MEASURE_2' is empty-sequence(). (Did you mean 'MEASURE_2'?)

at com.ddtek.xquery.typing.TypeVisitor.createException(TypeVisitor.java:4526)

at com.ddtek.xquery.typing.TypeVisitor.resolveStep(TypeVisitor.java:4618)

at com.ddtek.xquery.typing.TypeVisitor.visit(TypeVisitor.java:1181)

at com.ddtek.xquery.expr.AxisStep.accept(AxisStep.java:325)

at com.ddtek.xquery.typing.TypeVisitor.visit(TypeVisitor.java:3465)

at com.ddtek.xquery.expr.PathExpr.accept(PathExpr.java:108)

at com.ddtek.xquery.expr.AllExpressionVisitor.visit(AllExpressionVisitor.java:689)

at com.ddtek.xquery.typing.TypeVisitor.visit(TypeVisitor.java:4241)

at com.ddtek.xquery.expr.VarDecl.accept(VarDecl.java:503)

at com.ddtek.xquery.typing.TypeVisitor.visit(TypeVisitor.java:2486)

at com.ddtek.xquery.expr.FLWORExpr.accept(FLWORExpr.java:204)

at com.ddtek.xquery.expr.AllExpressionVisitor.visit(AllExpressionVisitor.java:309)

at com.ddtek.xquery.expr.DefaultExpressionList.accept(DefaultExpressionList.java:376)

at com.ddtek.xquery.expr.AllExpressionVisitor.visit(AllExpressionVisitor.java:284)

at com.ddtek.xquery.typing.TypeVisitor.visit(TypeVisitor.java:2061)

at com.ddtek.xquery.expr.DirElemConstructor.accept(DirElemConstructor.java:188)

at com.ddtek.xquery.expr.AllExpressionVisitor.visit(AllExpressionVisitor.java:309)

at com.ddtek.xquery.expr.DefaultExpressionList.accept(DefaultExpressionList.java:376)

at com.ddtek.xquery.expr.AllExpressionVisitor.visit(AllExpressionVisitor.java:284)

at com.ddtek.xquery.typing.TypeVisitor.visit(TypeVisitor.java:2061)

at com.ddtek.xquery.expr.DirElemConstructor.accept(DirElemConstructor.java:188)

at com.ddtek.xquery.expr.AllExpressionVisitor.visit(AllExpressionVisitor.java:455)

at com.ddtek.xquery.typing.TypeVisitor.visit(TypeVisitor.java:3113)

at com.ddtek.xquery.expr.MainModule.accept(MainModule.java:57)

at com.ddtek.xquery.typing.TypeVisitor.typeExpression(TypeVisitor.java:570)

at com.ddtek.xquery.mediator.ContextFactory$MediatorTypeVisitor.typeExpression(ContextFactory.java:515)

at com.ddtek.xquery.mediator.ContextFactory.prepareQuery(ContextFactory.java:197)

at com.ddtek.xquery3.xqj.layer.DDXQAbstractExpression.createOrGetExecutionContext(DDXQAbstractExpression.java:597)

... 2 more


Could this be happening because I don't have permission to access the xmlns documents?
Posttop
Ivan PedruzziSubject: Query DB and convert output to pre-defined XML format
Author: Ivan Pedruzzi
Date: 31 May 2007 11:07 PM
Edward

The query stops working because the default namespace is redefined changhing the semantic of all XPath expressions inside the dataset element

use the following code instead

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

<cognos:dataset
xmlns:cognos="http://developer.cognos.com/schemas/xmldata/1/"
xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
<cognos:metadata>
<cognos:item name="INDICATOR_KEY" type="xs:int"/>
<cognos:item name="AGENCY_GROUP_KEY" type="xs:int"/>
<cognos:item name="Agency" type="xs:string" length="20"/>
<cognos:item name="Indicator" type="xs:string" length="100"/>
<cognos:item name="IsDocument" type="xs:int"/>
<cognos:item name="AgencyDocument" type="xs:string" length="200"/>
<cognos:item name="DocumentKey" type="xs:string" length="50"/>
<cognos:item name="DocumentValue" type="xs:string" length="2000"/>
<cognos:item name="AgencyHref" type="xs:string" length="200"/>
<cognos:item name="LinkHref" type="xs:string" length="100"/>
<cognos:item name="LinkPage" type="xs:string" length="4"/>
</cognos:metadata>
<cognos:DATA>
{
for $MEASURE_2 in collection("PROD_DATA_DIC.dbo.MEASURE_2")/MEASURE_2,
$MEASURE_DEFINITION_2 in collection("PROD_DATA_DIC.dbo.MEASURE_DEFINITION_2")/MEASURE_DEFINITION_2
where $MEASURE_2/MEASURE_ID = $MEASURE_DEFINITION_2/MEASURE_ID
return
<cognos:ROW>
<cognos:VALUE>{$MEASURE_2/INDICATOR_KEY/text()}</cognos:VALUE>
...

</cognos:DATA>
</cognos:dataset>

   
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.