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)
-> + XML Publisher Generation of XS... (3)
-> + SS Error: Segment UNB: Segment... (6)
-> + SS Enterprise 2007 R2 does not... (2)
-> + Generating XSD from XML - glob... (3)
-> + SS Enterprise 2007 R2 does not... (2)
-> + SS dies on xsl files loads (7)
-> + EDIFACT UNA section ? (3)
-> + SS closes when I attempt to sa... (2)
-> + Support for SPEC 2000 & genera... (6)
-> + Uninstall failure (10)
-> + WSDL to JAVA code (2)
-> + Can't Connect to DB - SQL 2005... (3)
-> + Java editor (3)
-> + Trouble getting my xsl scenari... (6)
-> + Activation Key Not Compatible.... (2)
-> + Stylus Studio 2007 R2 and Regi... (3)
-> - XSD file displayed in Excel an... (1)
-> + What Is This vmm File? (5)
-> + Another in Release 2 (3)
-> + Error on Release 2 (3)
-> - Query DB and convert output to... (11)
-> ->Query DB and convert outp...
-> ->Query DB and convert outp...
-> ->Query DB and convert outp...
-> ->Query DB and convert outp...
-> ->Query DB and convert outp...
-> ->Query DB and convert outp...
-> ->Query DB and convert outp...
-> ->Query DB and convert outp...
-> ->Query DB and convert outp...
-> ->Query DB and convert outp...
-> + import static HTML into XML Pu... (2)
-> + saxon 8.9 include path resolut... (10)
-> + Can't drag field delimiter (2)
-> + Enterprise Edition Relase 2 up... (4)
-> + Upgrade to Enterprise Release ... (2)
-> + HTML to XSLT wizard missing in... (7)
-> + sqlxml.dll error after update ... (4)
-> + Printing the tree or the grid ... (2)
-> + Sorting problem with xsl[decim... (2)
-> + 2007 R2 Saxon: missing Convert... (4)
-> + EDIFACT D96A INVRPT into a SQL... (2)
-> + any addon Feature or tool that... (2)
-> + Catalog Assistance - (3)
-> + WYSIWYG does not show (3)
-> + HL7 support in Stylus (2)
-> + Xerces parser missing from Sty... (4)
-> - automatic DTD verification bef... (1)
-> + Try this (2)
-> - 2007 R2 java code generation -... (1)
-> + 2007 R2 Java built-in XSLT (4)
-> + 2007 R2 saxon 8.9 debug proble... (2)
-> + Stylus Studio Schema Validatio... (3)
-> + Bug: losing items when editing... (2)
-> + 2007 R2 Java built-in XSLT (2)
-> + XML->EDI: inconsistent treatme... (13)
-> + 2007 R2 global java classpath (2)
-> + New Web Service Call Failing (6)
-> + Grid view for nested tables (2)
-> - Revisit Old Problem from last ... (1)
-> + Install Question (3)
-> + hex value problem (5)
-> + WSDL Overview Diagram? (2)
-> + Outputing HTML from an XSLT 2.... (2)
-> + Stylus Studio Schema Publicati... (2)
-> + Re-usable XML Schema (3)
-> + .net xslt extension objects (6)
-> + InstallShield problem (2)
-> + Changing default XSLT processo... (2)
-> - SS incorrectly handles paramet... (1)
-> + Can't add file to project (7)
-> + Stylus invoking when i login i... (2)
-> + How to utilize Saxon (custom) ... (3)
-> + xsl:decimal-format (5)
-> + Hidden Costs of SS? (2)
-> + Eclipse Plugin available? (4)
-> + Having trouble with xpath quer... (2)
-> + Specifying processor to use wi... (3)
-> + MySQL or Generic JDBC Data Sup... (8)
-> + JAXB java generation doesn't s... (3)
-> - Eclipse (1)
-> + XSTL Debugger not working for ... (6)
-> + SS w/ Visual Studio and VSS (2)
-> + SS crashes on load (2)
-> + Can't find file ... then does,... (2)
-> + Diff folders not working (4)
-> + last node in preceding-sibling (3)
-> + SQLXML and Subquery (2)
-> + Xquery - No ReleationalDB opti... (3)
-> + Is a default adapter - not Tid... (8)
-> + Need actual (18)
-> + include path for external enti... (2)
-> + xpath query in SQL server (4)
-> + XPATH Query that works in 2006... (4)
-> + Need help with XML Assignment (3)
-> + Java Compiler in Windows Vista... (6)
-> + Sense:X Only Shows One Enumera... (3)
-> + adding tables and relationship... (6)
-> + preview automatically starts a... (12)
-> + French date in excel 97 using ... (2)
-> + How to maintain large XSL (2)
-> + French date in excel 97 using ... (2)
-> + How to pass EDIFACT as paramet... (3)
-> + C# Generation from XSD (5)
-> + Oracle RDBMS <-> .XSD (2)
-> + Support for Berkeley DB XML 2.... (2)
-> + conrext lost with xslt 2.0 for... (4)
-> + BizTalk schema problem (4)
-> + MSXML 6 properties resolveExte... (3)
-> + Deployment adapters and lookup... (2)
-- Previous [1621-1640] [1641-1660] [1661-1680] 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.