The first example is fairly easy, I just needed to get the date/time without the timezone modifier, in other words to convert this: 2016-01-29T00:00:00-08:00
to this: 2016-01-29T00:00:00.
The reason was that our web service was only coded to handle the date without the timezone. It used an Orcacle date/time conversion like this:
eValue = "TO_DATE('" + eValue.Replace("T", " ").Replace(".0000000", "") + "','YYYY-MM-DD HH24:MI:SS')";
XPath “Where” clause when using complex namespaces
Take a look at the data below. I needed to map the EventDate associated with DateType=PLN to D02, and the EventDate associated with DataType=FCT to D03.
Most of the examples on the internet show you how to do this when you don’t have namespaces involved. But when you do have namespaces, the trick is to use nested queries in your XPATH. In the sample below, you can see that the text in yellow is complete inside the other xpath brackets.
Using [local-name()=’xxx”] is like saying find me the element whose name matches ‘xxx’.
Once the where has been matched, the “xsl:value-of” statement begins with ./ to go up one level, then to find the EventDate (that is on the same level as DateType) to put in the output field.
XpatherTester.com is the current “go to” tool I use when I want to test XPath (online and for free). I prefer the one built-in to Stylus Studio, but that’s a paid for product that I cannot install on each client’s computer.
For example, I typed in //book/title below. I have highlighted in yellow, because at first, it’s not intuitively obvious where to actually type in the XPath. Note: Do not type in the XPath in the Namespaces: box!
Step 3- View your results
The tool will leave your original XML on one tab of your browser, then the results will be opened in a new tab on your browser. This picture shows the original tab on the left, and the new tab on the right.
Note that the “” xml wrapper is added to keep the results as well-formed XML. When you run your XPath in C# or other tools, that will not happen.
Step 4- Retry as needed
When doing a complicated XPath, you might have to try several times. Close the results tab in your browser, and go back to your original tab to try again.
If you want to specify something like //ns1:books you can define namespace ns1 in the namespaces textarea at the top of the screen. I haven’t placed with that much. I tend to create XPath that uses the “localname” syntax, which is a little more difficult. Use of this syntax is explained well on this StackOverflow page: http://stackoverflow.com/questions/10813653/xpath-select-node-based-in-a-condition-with-local-name.
//*[local-name()=’Link’]/*[@LinkID=’13’] (XPath returned 0 items)
//*[local-name()=’Links’]/[local-name()=’Links’ and @LinkID=’13’] ( Unexpected token “[” in path expression or Expression must evaluate to a node-set)
//[local-name()=’Links’]//*[local-name()=’Link’][@LinkID=’13’] ( Unexpected token “[” in path expression or Expression must evaluate to a node-set) – This is same as one that worked below except // instead of //*
What works are these:
//*[local-name()=’Link’ and @LinkID=’13’]
//*[local-name()=’Link’][@LinkID=’13’]
//*[local-name()=’Links’]//*[local-name()=’Link’][@LinkID=’13’]
When BizTalk creates a sample instance, it creates it in this structure (except below, more realistic data has been entered).
<books>
<book>
<title>A Tale of Two Cities</title>
<authorLastname>Dickens</authorLastname>
<authorFirstname>Charles</authorFirstname>
<category>fiction</category>
</book>
<book>
<title>Operating Manual for Spaceship Earth</title>
<authorLastname>Fuller</authorLastname>
<authorFirstname>Buckminster</authorFirstname>
<category>science</category>
</book>
<book>
<title>The Singularity Is Near: When Humans Transcend Biology</title>
<authorLastname>Kurzweil </authorLastname>
<authorFirstname>Ray</authorFirstname>
<category>science</category>
</book>
<book>
<title>The Age of Spiritual Machines: When Computers Exceed Human Intelligence</title>
<authorLastname>Kurzweil </authorLastname>
<authorFirstname>Ray</authorFirstname>
<category>science</category>
</book>
</books>
/books/book/title – returns all titles
/books/book/category[.=’science’] – returns all categories, where the text of that category = ‘science’
/books/book[category=’science’]/title – returns all titles, where the text of that category = ‘science’
/books/book[category=’science’ and authorFirstname=’Ray’]/title – return all titles where Ray is the author and category = ‘science’
When BizTalk creates a sample instance, it creates it in this structure (except below, more realistic data has been entered).
Each “book” record has it’ s own name space. This data was created with a ‘books’ schema, which in turn imported a “book” schema.
The book record was defined by adding a new “record”, and settings its property “Data Structure (Type):
<ns0:books xmlns:ns0=”http://biztalk-training.com/books”>
<ns1:book xmlns:ns1=”http://biztalk-training.com/book”>
<title>A Tale of Two Cities</title>
<authorLastname>Dickens</authorLastname>
<authorFirstname>Charles</authorFirstname>
<category>fiction</category>
</ns1:book>
<ns1:book xmlns:ns1=”http://biztalk-training.com/book”>
<title>Operating Manual for Spaceship Earth</title>
<authorLastname>Fuller</authorLastname>
<authorFirstname>Buckminster</authorFirstname>
<category>science</category>
</ns1:book>
<ns1:book xmlns:ns1=”http://biztalk-training.com/book”>
<title>The Singularity Is Near: When Humans Transcend Biology</title>
<authorLastname>Kurzweil</authorLastname>
<authorFirstname>Ray</authorFirstname>
<category>science</category>
</ns1:book>
<ns1:book xmlns:ns1=”http://biztalk-training.com/book”>
<title>The Age of Spiritual Machines: When Computers Exceed Human Intelligence</title>
<authorLastname>Kurzweil</authorLastname>
<authorFirstname>Ray</authorFirstname>
<category>science</category>
</ns1:book>
</ns0:books>
When BizTalk creates a file from the mapping utility, it combines the namespaces onto the root element, and uses a different prefix, as shown below:
<ns0:books xmlns:ns0=”http://biztalk-training.com/books” xmlns:bk=”http://biztalk-training.com/book”>
<bk:book>
<title>A Tale of Two Cities</title>
<authorLastname>Dickens</authorLastname>
<authorFirstname>Charles</authorFirstname>
<category>fiction</category>
</bk:book>
<bk:book>
<title>Operating Manual for Spaceship Earth</title>
<authorLastname>Fuller</authorLastname>
<authorFirstname>Buckminster</authorFirstname>
<category>science</category>
</bk:book>
<bk:book>
<title>The Singularity Is Near: When Humans Transcend Biology</title>
<authorLastname>Kurzweil</authorLastname>
<authorFirstname>Ray</authorFirstname>
<category>science</category>
</bk:book>
<bk:book>
<title>The Age of Spiritual Machines: When Computers Exceed Human Intelligence</title>
<authorLastname>Kurzweil</authorLastname>
<authorFirstname>Ray</authorFirstname>
<category>science</category>
</bk:book>
</ns0:books>
/*[local-name()=’books’ and namespace-uri()=’http://biztalk-training.com/books’]/*[local-name()=’book’ and namespace-uri()=’http://biztalk-training.com/book’]/title
If for some reason, you had a namespace on category, the same query could be done like this:
/*[local-name()=’books’]/*[local-name()=’book’][*[local-name()=’category’][.=’science’]]/title
/*[local-name()=’books’]/*[local-name()=’book’][category=’science’ and authorFirstname=’Ray’]/title
Thanks to the support team at “StylusStudio.com” for helping with these concepts in their forums. Click here for –> My discussion thread on these issues
The trick they revealed is that the [] expressions can be nested. Here’s another post, where we talked about selecting a higher node: link.
That’s the end of the XPath lesson, if you want to see how the “Books” schema was created, see here… BizTalk Schema Imports.
