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
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
Alberto MassariSubject: Query DB and convert output to pre-defined XML format
Author: Alberto Massari
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>

 
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.