SSDN - Please help convert SQL query to Xquery

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Vincent Visconti

Subject: Please help convert SQL query to Xquery
Author: Vincent Visconti
Date: 12 Jan 2010 11:30 AM

Hello,

I am new to Xquery and have been trying to find some examples and/or documentation on how to represent an sql select query in xquery but for some reason I am finding it very difficult to find some solid detailed documentation on how to do this.

I have established a connection to my IBM DB2 database and have a collection defined in Xquery file as: collection("SPFPRDDTA.PRL1HDRR00")

I need to know how to write an Xquery statement that is the equivalent to this sql statement:

SELECT
TRIM(XHTAXYR) AS dXHTAXYR,
TRIM(XHPKGTYP) AS dXHPKGTYP,
TRIM(XHSLIPSRC) AS dXHSLIPSRC,
TRIM(XHTRANSNUM) AS dXHTRANSNUM,
TRIM(XHTYPE) AS dXHTYPE,
TRIM(XHTRNML1) AS dXHTRNML1,
TRIM(XHTRNML2) AS dXHTRNML2,
TRIM(XHTRADL1) AS dXHTRADL1,
TRIM(XHTRADL2) AS dXHTRADL2,
TRIM(XHTRCTY) AS dXHTRCTY,
TRIM(XHTRPROV) AS dXHTRPROV,
TRIM(XHTRPSTLCD) AS dXHTRPSTLCD,
TRIM(XHTECHCNTN) AS dXHTECHCNTN,
TRIM(XHTECHARCD) AS dXHTECHARCD,
TRIM(XHTECHTNBR) AS dXHTECHTNBR,
TRIM(XHTECHXNBR) AS dXHTECHXNBR,
TRIM(XHTECHLANG) AS dXHTECHLANG,
TRIM(XHACCTCNTN) AS dXHACCTCNTN,
TRIM(XHACCTARCD) AS dXHACCTARCD,
TRIM(XHACCTTNBR) AS dXHACCTTNBR,
TRIM(XHACCTXNBR) AS dXHACCTXNBR,
TRIM(XHACCTLANG) AS dXHACCTLANG,
TRIM(XHDEVAUTHN) AS dXHDEVAUTHN
FROM PRL1HDRR00

Any help would be much appreciated.

Thank you.

Vincent

Vincent Visconti

Subject: Please help convert SQL query to Xquery
Author: Vincent Visconti
Date: 12 Jan 2010 03:41 PM

OK, so this is what I came up with, after watching one of the tutorial videos and fooling around with xquery mapper.

<root>
{
for $PRL1HDRR00 in collection("SPFPRDDTA.PRL1HDRR00")/PRL1HDRR00
return
<row>

<dXHTAXYR>
{
normalize-space($PRL1HDRR00/XHTAXYR/text())
}
</dXHTAXYR>
<dXHPKGTYP>
{
normalize-space($PRL1HDRR00/XHPKGTYP/text())
}
</dXHPKGTYP>
<dXHSLIPSRC>
{
normalize-space($PRL1HDRR00/XHSLIPSRC/text())
}
</dXHSLIPSRC>
<dXHTRANSNUM>
{
normalize-space($PRL1HDRR00/XHTRANSNUM/text())
}
</dXHTRANSNUM>
<dXHTYPE>
{
normalize-space($PRL1HDRR00/XHTYPE/text())
}
</dXHTYPE>
<dXHTRNML1>
{
normalize-space($PRL1HDRR00/XHTRNML1/text())
}
</dXHTRNML1>
<dXHTRNML2>
{
normalize-space($PRL1HDRR00/XHTRNML2/text())
}
</dXHTRNML2>
<dXHTRADL1>
{
normalize-space($PRL1HDRR00/XHTRADL1/text())
}
</dXHTRADL1>
<dXHTRADL2>
{
normalize-space($PRL1HDRR00/XHTRADL2/text())
}
</dXHTRADL2>
<dXHTRCTY>
{
normalize-space($PRL1HDRR00/XHTRCTY/text())
}
</dXHTRCTY>
<dXHTRPROV>
{
normalize-space($PRL1HDRR00/XHTRPROV/text())
}
</dXHTRPROV>
<dXHTRPSTLCD>
{
normalize-space($PRL1HDRR00/XHTRPSTLCD/text())
}
</dXHTRPSTLCD>
<dXHTECHCNTN>
{
normalize-space($PRL1HDRR00/XHTECHCNTN/text())
}
</dXHTECHCNTN>
<dXHTECHARCD>
{
normalize-space($PRL1HDRR00/XHTECHARCD/text())
}
</dXHTECHARCD>
<dXHTECHTNBR>
{
normalize-space($PRL1HDRR00/XHTECHTNBR/text())
}
</dXHTECHTNBR>
<dXHTECHXNBR>
{
normalize-space($PRL1HDRR00/XHTECHXNBR/text())
}
</dXHTECHXNBR>
<dXHTECHLANG>
{
normalize-space($PRL1HDRR00/XHTECHLANG/text())
}
</dXHTECHLANG>
<dXHACCTCNTN>
{
normalize-space($PRL1HDRR00/XHACCTCNTN/text())
}
</dXHACCTCNTN>
<dXHACCTARCD>
{
normalize-space($PRL1HDRR00/XHACCTARCD/text())
}
</dXHACCTARCD>
<dXHACCTTNBR>
{
normalize-space($PRL1HDRR00/XHACCTTNBR/text())
}
</dXHACCTTNBR>
<dXHACCTXNBR>
{
normalize-space($PRL1HDRR00/XHACCTXNBR/text())
}
</dXHACCTXNBR>
<dXHACCTLANG>
{
normalize-space($PRL1HDRR00/XHACCTLANG/text())
}
</dXHACCTLANG>
<dXHDEVAUTHN>
{
normalize-space($PRL1HDRR00/XHDEVAUTHN/text())
}
</dXHDEVAUTHN>
</row>
}
</root>

I know there isn't a right or wrong way of getting the result you need, and I'm hoping this will get me to where I need to be. I guess what I want to know now is if there is a better way of doing this than manually adding nodes to a target document, typing their names, and then linking the columns from my table?

Thanks,

Vincent

Vincent Visconti

Subject: Please help convert SQL query to Xquery
Author: Vincent Visconti
Date: 12 Jan 2010 04:15 PM

ok, now I want to use this xquery file as input to my xslt file. It doesn't seem to be working, anything look wrong below?

<?xml version='1.0' encoding='utf-8' ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
<Transmission VersionSchema="2009.1.1" xmlns="http://www.mrq.gouv.qc.ca/T5">
<xsl:for-each select="document('db:///PRL1HDRR00.xquery')/root/row">
<P>

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 to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

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.