XQuery for the SQL programmer – Why XQuery?

Over the last month, wearing our SQL-glasses we mastered the basics of XQuery. But wait... what we’ve learned up to now in the XQuery for the SQL programmer series is about “do in XQuery what you can do in SQL”. Doesn’t sound like a compelling reason to use XQuery

To be clear, I’m not advocating to replace SQL with XQuery. For a lot of good reasons SQL is there today, and it will be there tomorrow. Admitting, that’s also a good thing for DataDirect, as a fair amount of our business is based on ODBC, JDBC and ADO.NET.

Why do I want to use XQuery against relational databases? Minollo answered the question before. It’s all about the data integration promise offered by products like DataDirect’s Data Integration Suite.

If you frequent xml-connection.com you know about the use cases

• Publishing relational data as XML
• Shredding XML into an RDBMS
• Aggregating your LDAP repository with relational data
• Developing WebServices
• Interfacing through EDI messages
• Handling proprietary formats
• Bulk loading of XML
• Joining XML with your relational database
• Querying WebServices
• Reading office documents to update your database
• Producing EDI messages

Are you suffering data integration?

Tech Tags: XQuery SQL

Generating non-XML files from XQuery is easier than you think!

There is an ongoing discussion thread on a popular XQuery discussion list. The topic is about how to generate a non-XML result (CSV in the specific case discussed there) from an XQuery. Seems easy, but there are a lot of little issues in doing so, especially with the handling of whitespaces.
Ivan, who is Program Manager for the XML products here at DataDirect, has chimed in describing how easily DataDirect XQuery can leverage XML Converters to make XQuery generation of non-XML results easy. The XML document that needs to be transformed into CSV looks like this:<br /><?xml version='1.0'?><br /><list><br /> <entry><br /> <property1>A</property1><br /> <property2>33</property2><br /> <quantity>1,5</quantity><br /> </entry><br /> <entry><br /> <property1>A</property1><br /> <property2>33</property2><br /> <quantity>0,5</quantity><br /> </entry><br /> <entry><br /> <property1>B</property1><br /> <property2>22</property2><br /> <quantity>0,5</quantity><br /> </entry><br /></list><br />...the solution proposed by Ivan is indeed quite simple and elegant:<br />declare option ddtek:serialize "method=CSV";<br />/<br />...looks pretty easy, doesn't it? What the "method=CSV" option is telling DataDirect XQuery is to use the CSV XML Converter to serialize data; XML Converters transform a ...... structure into CSV; there are of course a number of options available to tune that behavior (like, for example, listing the field names in the first row), but the underlying mechanism is quite simple. XML Converters use the same approach for other conversions, and the same mechanism is available from XQuery: make XQuery generate an XML structure consistent with what XML Converters expect (and you can see that asking XML Converters to generate the XML schema for a specific conversion through tools or API), and then just specify the proper conversion method (and options) in the ddtek:serialize option. We have seen the same concept in other posts on this blog, like this one related to EDI, for example.
In the above mention discussion thread, the original poster goes on asking how to achieve something a bit more complicated: "I am not just copying row-type elements from the input file, but rather summing up the values found in the "quantity" children of the entries, after grouping them by the content of "property1" and
"property2" (see the first message in this thread)."
Easy enough; just reusing the example posted at the beginning of the email thread, you can do:<br />declare option ddtek:serialize "method=CSV:sep=;";<br /><root> {<br /> let $entries := /list/entry<br /> for $property1 in distinct-values($entries/property1),<br /> $property2 in distinct-values($entries[property1 = $property1]/property2)<br /> let $selected_entries := $entries[property1 = $property1 and property2 = $property2]<br /> order by $property1, $property2<br /> return<br /> <row><br /> <field1>{$property1}</field1>,<br /> <field2>{$property2}</field2>,<br /> <sum>{ replace(xs:string(<br /> sum(for $quantity in $selected_entries/quantity<br /> return xs:decimal(replace($quantity, ",", ".")))),<br /> "\.", ",")<br /> } </sum><br /> </row><br />} </root><br /> The result, is what you would expect:
A;33;2
B;22;0,5

As already noted on this Blog, XQuery is a great language for querying, transforming, manipulating XML and other data sources (RDBMS, EDI, a combination of all of them); when used in conjunction with XML Converters, you are able to bridge between the XML and non-XML world very easily, quickly creating very powerful solutions.

XQuery, data sources and XML

Recently I started reading this Blog entry about LINQ and Entity Framework, and I noticed that the first sentence sounds very similar to what we usually say when we discuss DataDirect XQuery:
The problem with data is that it exists in many formats and until now has required that developers learn multiple competing technologies.

Very true; I would argue that there are many more problems related to data in today's IT organizations, but, still, it is true that data is available in many different formats, and that developers need to deal with a large variety of different data models and interfaces to manipulate data.
The author goes on saying:
If you want to query Active Directory, for example, you have to learn Lightweight Directory Access Protocol (LDAP). If you want to query a database, you need to learn SQL.

If you have read some of our posts on xml-connection.com, you'll know that we are aware of that pain; and that we provided examples about how to create XQueries that handle LDAP, RDBMS, and combination of more data sources (Web services, EDI, proprietary formats, ...).
But then, I read:
If you want to manage XML data, you have to learn XPath (or XQuery)

Well... yes, but that's the *only* thing you need to learn! The main point around XQuery is being missed here; not only XQuery is a great way to manipulate, query, transform data or documents that are physically available as XML; XQuery is a great way to work against a variety of heterogeneous data sources, physically very different from each other (RDBMS, LDAP, EDI, CSV, proprietary text files), but that can all be abstracted using the XML Data Model. XQuery, and our DataDirect XQuery in particular, helps solving the problem described in the first few lines of the referenced Blog entry.
So, please, when you talk about XQuery don't just consider it yet another XML query or transformation language; XQuery can be much more than that, and can help simplifying a large variety of problems where you need to deal with heterogeneous data sources individually or joining them together! If you have a chance, just browse through the topics in this Blog, and I'm sure you'll see what I'm talking about.