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
Sebastien VinchonSubject: Way to replace codes by labels stored in an DB table depending on language and location in an XML file using Data Direct engine
Author: Sebastien Vinchon
Date: 13 Jun 2008 12:17 PM
Originally Posted: 13 Jun 2008 07:15 AM
Hello XML gurus,

A prospect of mine is looking for a solution to generate XML with readable labels in various languages out of two inputs:
- an input file containing only label codes, a language code and a location code
- a translation table stored in a DB containing all the readable labels together with the language code and location code they go with.

The attached ZIP files contains various files I have created to try to illustrate this requirement:
1. TranslationTableRequirementExample_SampleInput.xml illustrates the kind of XML input data we could get as an input
2. TranslationTableRequirementExample_SampleTranslationTable.txt illustrates the kind of data my prospect would like to continue to maintain in an external database table
3. TranslationTableRequirementExample_ExpectedOutput.xml illustrates what the pre-processing step we are currently trying to find a tool for would produce out of the input data and the data stored in the external database table
4. TranslationTableRequirementExample_PossibleAlgorithm.txt illustrates the kind of algorithm that could be used if the transformation was done by custom code

I am trying to determine if the Data Direct xQuery engine could deal with this requirement in an efficient way.

So my questions are:
1. Can Data Direct xQuery engin satisfy the requirement illustrated by the attached files?
2. How would it work?
3. Can someone provide an example illustrating how it would work based on the files I provided?

Many thanks for any help.

I've always been impressed by the quality of the responses on this forum and I hope it will be the same this time!

This is pretty urgent...


UnknownTranslationTableRequirementExample.zip

Postnext
Alberto MassariSubject: Way to replace codes by labels stored in an DB table depending on language and location in an XML file using Data Direct engine
Author: Alberto Massari
Date: 13 Jun 2008 11:56 AM
Hi Sebastian,
I could not try the query against live data, but this should at least give you a starting point.

1) create a new query
2) show the File Explorer window, expand the Relational DB folder, add a server for your database
3) expand the db server until you find the table to be used, then drag it to the right pane of the XQuery
4) paste this content as the query


declare function local:convert($node as element(), $codes as element()*)
{
element { node-name($node) } {
$node/@*,
for $n in $node/node()
return
if($codes[LabelCode=$n/text()]) then
element { node-name($n) } { $codes[LabelCode=$n/text()]/Label }
else
typeswitch ($n)
case element() return local:convert($n, $codes)
default return $n
}
};

<Documents>
{
for $document in doc("file:///c:/testcase/TranslationTableRequirementExample_SampleInput.xml")/Documents/Document
return
<Document>
{
for $customer in $document/CustomerInformation
let $codes := collection("xxxxx")/*[Language=$customer/Language and Location=$customer/Location]
return
local:convert($customer, $codes)
}
</Document>
}
</Documents>

5) drag the table to be used as lookup table on the collection("xxxxx") text, so that the correct URL is generated.

Hope this works,
Alberto

Postnext
Sebastien VinchonSubject: Way to replace codes by labels stored in an DB table depending on language and location in an XML file using Data Direct engine
Author: Sebastien Vinchon
Date: 13 Jun 2008 01:01 PM
Many thank for your feedback.

I created a test table in SQL Server and tried your code, but I get an error.

See attached Word document with screen captures.

Can you tell me what is wrong?


UnknownxQueryTestResult.doc

Posttop
Alberto MassariSubject: Way to replace codes by labels stored in an DB table depending on language and location in an XML file using Data Direct engine
Author: Alberto Massari
Date: 13 Jun 2008 02:36 PM
Hi Sebastien,
it looks DDXQ is not able to get the type of the column of the database. You should try changing the two occurrences of [LabelCode=$n/text()] into [LabelCode/text()=$n/text()]; if this still doesn't work, change them into [string(LabelCode/text())=string($n/text())].
You should also add a /text() at the end of the $codes[...]/Label, or you will get an extra node around the translation.

Alberto

 
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.