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 » XQuery Help and Discussion » Cross Referencing Country Names
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
+ Stylus Studio Technical Forum (14621)
+ Website Feedback (249)
+ XSLT Help and Discussion (7625)
- XQuery Help and Discussion (2017)
-> + Issue with Processing Instruct... (2)
-> + problem converting json to XML... (2)
-> + Problem base64 decoding string... (3)
-> + Problems posting multipart for... (5)
-> + trouble with download of price... (2)
-> + Problem with http-post not bei... (3)
-> + path problem, xps_file:writeAl... (9)
-> + Xquery update support? (2)
-> + problem with Stylus studio try... (5)
-> + adding dtd reference to xml ou... (4)
-> + xquery escaping ambarsand when... (3)
-> + Whitespace problem when return... (5)
-> + Problem with namespace prefix ... (5)
-> - Sending via SFTP returns unexp... (1)
-> + Query and Sftp clent (4)
-> + xquery and try - catch (3)
-> + Query + ddtek:http-post optio... (5)
-> + Example files referenced in do... (3)
-> + Automatic Error Detection and ... (3)
-> + Working with result of ddtek:h... (2)
-- [1-20] [21-40] [41-60] Next
+ Stylus Studio FAQs (159)
+ Stylus Studio Code Samples & Utilities (364)
+ Stylus Studio Announcements (113)
Topic  
Postnext
chris miszturSubject: Cross Referencing Country Names
Author: chris misztur
Date: 10 Jun 2013 02:17 PM
Hello.

I am trying to normalize an XML file from our legacy system. In the legacy system the country names are not constrained, so anything from USA, US, United States to U.S.A. is valid. In the target system, the country field is limited to 3 characters and must match the country codes in a control table. I was thinking of using a cross-reference XML document to find a country match.

Cross-Ref Document:
<Countries>
<Country LegacyName="USA" DRKY="US">
<Country LegacyName="CANADA" DRKY="CA">
<Country LegacyName="MEXICO" DRKY="MX">
<Country LegacyName="POLAND" DRKY="PL">
</Countries>

The DRKY attribute is actually a key to the JDEdwards country lookup table (F0005). As a second step I'd like to be able to lookup other data columns in the JDE table based on the key.

Would this be a good place to start:

http://www.stylusstudio.com/ssdn/default.asp?action=9&read=8422&fid=57

Postnext
chris miszturSubject: Cross Referencing Country Names
Author: chris misztur
Date: 10 Jun 2013 02:58 PM
I tried the below but it returns all countries' DRKY values. How can find a single match?

for $Country in $CountryLookups/Countries/Country
return
if(upper-case($Country/@LegacyName) = upper-case($AddressEntry/a:SYS_CO/text())) then (upper-case($Country/@DRKY))
else ($AddressEntry/a:SYS_CO/text())
Postnext
chris miszturSubject: Cross Referencing Country Names
Author: chris misztur
Date: 10 Jun 2013 03:57 PM
This seems to do the trick. Am I doing it the most efficient way? Should I cache the search in the if() predicate so I don't search twice ?

if(exists($CountryLookups/Countries/Country[@LegacyName = $AddressEntry/a:SYS_CO/text()]))
then (data($CountryLookups/Countries/Country[@LegacyName = $AddressEntry/a:SYS_CO/text()]/@DRKY))
else ($AddressEntry/a:SYS_CO/text())
Posttop
Ivan PedruzziSubject: Cross Referencing Country Names
Author: Ivan Pedruzzi
Date: 11 Jun 2013 12:22 PM

A good query optimizer does the right thing, here a more compact version

let $p := $CountryLookups/Countries/Country[@LegacyName = $AddressEntry/a:SYS_CO/text()]
return
if(exists($p)) then data($p/@DRKY))
else $AddressEntry/a:SYS_CO/text()



Ivan Pedruzzi
Stylus Studio Team
   
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.