XQuery for the Rest of Us!

Data Integration with DataDirect XQuery®

Many applications need to process information from both XML documents and relational databases. DataDirect XQuery® lets you query both kinds of sources, integrating information from multiple data sources in one XML query result. And when you use DataDirect XQuery® together with DataDirect XML converters, you can also convert many non-XML formats into XML on the fly, including thousands of versions and sub-versions of EDI messages (X12 and EDIFACT), along with other formats like tab delimited, comma separated value, dBase files, and many more.

Querying XML and Relational Data

In DataDirect XQuery®, input may be either XML or relational. The result of an XQuery is generally XML. The following image shows an XML document which contains data used to query the relational sources. The XML document is a message that asks for the portfolio for a given user within a data range. Using XQuery, it is easy to create even complex nested XML documents by querying XML documents and relational data.

Figure 1: Querying Relational and XML Input

And creating XML output is the first step toward publishing XML as HTML, PDF, PostScript, or other formats.

Querying Web Services

Web services return XML, and XQuery is one of the easiest ways to process the result of a web service. And with DataDirect XQuery®, you can issue a web service call inside your query with a simple external Java function. For instance, here is a web service call that gets a stock quote from http://swanandmokashi.com using the function ddtek:wscall(). The first parameter gives the location of the web service; the second parameter contains the payload of the SOAP request.

ddtek:wscall( 
  <ddtek:location address= 
       "http://www.swanandmokashi.com/HomePage/WebServices/StockQuotes.asmx"  
    soapaction="http://swanandmokashi.com/GetQuotes" />
  , 
  <s0:GetQuotes xmlns:s0="http://swanandmokashi.com"> 

     <s0:QuoteTicker>prgs</s0:QuoteTicker>
  </s0:GetQuotes>

)

The ddtek:wscall() function returns only the payload of the query response. Here is the result of the above query.

<GetQuotesResponse xmlns="http://swanandmokashi.com">
   <GetQuotesResult>
      <Quote>
         <CompanyName>PROGRESS SOFTWARE</CompanyName>
         <StockTicker>PRGS</StockTicker>

         <StockQuote>25.77</StockQuote>
         <LastUpdated>5/15/2006 3:30pm</LastUpdated>
         <Change>-0.04</Change>
         <OpenPrice>25.65</OpenPrice>

         <DayHighPrice>26.00</DayHighPrice>
         <DayLowPrice>25.61</DayLowPrice>
         <Volume>408507</Volume>
         <MarketCap>1.059B</MarketCap>

         <YearRange>25.73 - 35.84</YearRange>
         <ExDividendDate>24-Jan-00</ExDividendDate>
         <DividendYield>N/A</DividendYield>
         <DividendPerShare>0.00</DividendPerShare>

      </Quote>
   </GetQuotesResult>
</GetQuotesResponse>

Obviously, this is simply XML that can be queried along with other XML and relational data sources.

XQuery for EDI and Flat File Formats

Unfortunately, many useful sources of data are not available as XML. But with DataDirect XML Converters®, you can convert many formats to XML on the fly, so they can be queried with DataDirect XQuery®. Converters are available for EDI messages (X12, and EDIFACT), along with other formats like tab delimited, comma separated value, dBase files, and many more.

For instance, there are literally thousands of versions and subversions of EDI formats, and no standard way to query or process them. XML Converters allow you to read any of these formats without writing code to parse them.

Example 1. An EDI Message converted to XML

EDI Message
UNA:+.? 'UNB+UNOA:4+STYLUSSTUDIO:1+DATADIRECT:1+20051107:1159+600
2'UNH+SSDD1+ORDERS:D:03B:UN:EAN008'BGM+220+BKOD99+9'DTM+137:20051
107:102'NAD+BY+5412345000176::9'NAD+SU+4012345000094::9'LIN+1+1+0
764569104:IB'QTY+1:25'FTX+AFM+1++XPath 2.0 Programmer?'s Referenc
e'LIN+2+1+0764569090:IB'QTY+1:25'FTX+AFM+1++XSLT 2.0 Programmer?'
s Reference'LIN+3+1+1861004656:IB'QTY+1:16'FTX+AFM+1++Java Server 
Programming'LIN+4+1+0596006756:IB'QTY+1:10'FTX+AFM+1++Enterprise 
Service Bus'UNS+S'CNT+2:4'UNT+22+SSDD1'UNZ+1+6002'
XML Representation of EDI Message
<?xml version="1.0" encoding="utf-8"?>

<EDIFACT>
    <UNB>
        <UNB01>
            <UNB0101><!--0001: Syntax identifier-->UNOA</UNB0101>
            <UNB0102><!--0002: Syntax version number-->4</UNB0102>

        </UNB01>
        <UNB02>
            <UNB0201><!--0004: Interchange sender identification
				-->STYLUSSTUDIO</UNB0201>
            <UNB0202><!--0007: Identification code qualifier
				-->1</UNB0202>

        </UNB02>
        <UNB03>
            <UNB0301><!--0010: Interchange recipient identification-->
				DATADIRECT</UNB0301>
            <UNB0302><!--0007: Identification code qualifier-->
				1</UNB0302>

        </UNB03>
        <UNB04>
            <UNB0401><!--0017: Date-->20051107</UNB0401>
            <UNB0402><!--0019: Time-->1159</UNB0402>

        </UNB04>
        <UNB05><!--0020: INTERCHANGE CONTROL REFERENCE-->6002</UNB05>
    </UNB>
    <ORDERS>
        <UNH>

            <UNH01><!--0062: MESSAGE REFERENCE NUMBER-->SSDD1</UNH01>
            <UNH02>
                <UNH0201><!--0065: Message type-->ORDERS</UNH0201>
                <UNH0202><!--0052: Message version number-->D</UNH0202>

                <UNH0203><!--0054: Message release number-->03B</UNH0203>
                <UNH0204><!--0051: Controlling agency, coded-->UN</UNH0204>
                <UNH0205><!--0057: Association assigned code-->EAN008</UNH0205>

            </UNH02>
        </UNH>
        <BGM>
            <BGM01>
                <BGM0101><!--1001: Document name code-->220</BGM0101>

            </BGM01>
            <BGM02>
                <BGM0201><!--1004: Document identifier-->BKOD99</BGM0201>
            </BGM02>
            <BGM03><!--1225: Message function code-->9</BGM03>

        </BGM>
        <DTM>
            <DTM01>
                <DTM0101><!--2005: Date or time or period 
			function code qualifier-->137</DTM0101>
                <DTM0102><!--2380: Date or time or period 
			text-->20051107</DTM0102>

                <DTM0103><!--2379: Date or time or period format code-->102</DTM0103>
            </DTM01>
        </DTM>
        <GROUP_2>
            <NAD>

                <NAD01><!--3035: Party function code qualifier-->BY</NAD01>
                <NAD02>
                    <NAD0201><!--3039: Party identifier-->5412345000176</NAD0201>
                    <NAD0203><!--3055: Code list responsible agency code-->9</NAD0203>

                </NAD02>
            </NAD>
        </GROUP_2>
        <GROUP_2>
            <NAD>
                <NAD01><!--3035: Party function code qualifier-->SU</NAD01>

                <NAD02>
                    <NAD0201><!--3039: Party identifier-->4012345000094</NAD0201>
                    <NAD0203><!--3055: Code list responsible agency code-->9</NAD0203>
                </NAD02>

            </NAD>
        </GROUP_2>
        <GROUP_28>
            <LIN>
                <LIN01><!--1082: LINE ITEM IDENTIFIER-->1</LIN01>

                <LIN02><!--1229: ACTION REQUEST/NOTIFICATION DESCRIPTION CODE-->1</LIN02>
                <LIN03>
                    <LIN0301><!--7140: Item identifier-->0764569104</LIN0301>
                    <LIN0302><!--7143: Item type identification code-->IB</LIN0302>

                </LIN03>
            </LIN>
            <QTY>
                <QTY01>
                    <QTY0101><!--6063: Quantity type code qualifier-->1</QTY0101>

                    <QTY0102><!--6060: Quantity-->25</QTY0102>
                </QTY01>
            </QTY>
            <FTX>
                <FTX01><!--4451: TEXT SUBJECT CODE QUALIFIER-->AFM</FTX01>

                <FTX02><!--4453: FREE TEXT FUNCTION CODE-->1</FTX02>
                <FTX04>
                    <FTX0401><!--4440: Free text-->
					XPath 2.0 Programmer's Reference</FTX0401>
                </FTX04>

            </FTX>
        </GROUP_28>
        <GROUP_28>
            <LIN>
                <LIN01><!--1082: LINE ITEM IDENTIFIER-->2</LIN01>

                <LIN02><!--1229: ACTION REQUEST/NOTIFICATION DESCRIPTION CODE-->1</LIN02>
                <LIN03>
                    <LIN0301><!--7140: Item identifier-->0764569090</LIN0301>
                    <LIN0302><!--7143: Item type identification code-->IB</LIN0302>

                </LIN03>
            </LIN>
            <QTY>
                <QTY01>
                    <QTY0101><!--6063: Quantity type code qualifier-->1</QTY0101>

                    <QTY0102><!--6060: Quantity-->25</QTY0102>
                </QTY01>
            </QTY>
            <FTX>
                <FTX01><!--4451: TEXT SUBJECT CODE QUALIFIER-->AFM</FTX01>

                <FTX02><!--4453: FREE TEXT FUNCTION CODE-->1</FTX02>
                <FTX04>
                    <FTX0401><!--4440: Free text-->
					XSLT 2.0 Programmer's Reference</FTX0401>
                </FTX04>

            </FTX>
        </GROUP_28>
        <GROUP_28>
            <LIN>
                <LIN01><!--1082: LINE ITEM IDENTIFIER-->3</LIN01>

                <LIN02><!--1229: ACTION REQUEST/NOTIFICATION DESCRIPTION CODE-->1</LIN02>
                <LIN03>
                    <LIN0301><!--7140: Item identifier-->1861004656</LIN0301>
                    <LIN0302><!--7143: Item type identification code-->IB</LIN0302>

                </LIN03>
            </LIN>
            <QTY>
                <QTY01>
                    <QTY0101><!--6063: Quantity type code qualifier-->1</QTY0101>

                    <QTY0102><!--6060: Quantity-->16</QTY0102>
                </QTY01>
            </QTY>
            <FTX>
                <FTX01><!--4451: TEXT SUBJECT CODE QUALIFIER-->AFM</FTX01>

                <FTX02><!--4453: FREE TEXT FUNCTION CODE-->1</FTX02>
                <FTX04>
                    <FTX0401><!--4440: Free text-->Java Server Programming</FTX0401>
                </FTX04>

            </FTX>
        </GROUP_28>
        <GROUP_28>
            <LIN>
                <LIN01><!--1082: LINE ITEM IDENTIFIER-->4</LIN01>

                <LIN02><!--1229: ACTION REQUEST/NOTIFICATION DESCRIPTION CODE-->1</LIN02>
                <LIN03>
                    <LIN0301><!--7140: Item identifier-->0596006756</LIN0301>
                    <LIN0302><!--7143: Item type identification code-->IB</LIN0302>

                </LIN03>
            </LIN>
            <QTY>
                <QTY01>
                    <QTY0101><!--6063: Quantity type code qualifier-->1</QTY0101>

                    <QTY0102><!--6060: Quantity-->10</QTY0102>
                </QTY01>
            </QTY>
            <FTX>
                <FTX01><!--4451: TEXT SUBJECT CODE QUALIFIER-->AFM</FTX01>

                <FTX02><!--4453: FREE TEXT FUNCTION CODE-->1</FTX02>
                <FTX04>
                    <FTX0401><!--4440: Free text-->Enterprise Service Bus</FTX0401>
                </FTX04>

            </FTX>
        </GROUP_28>
        <UNS>
            <UNS01><!--0081: SECTION IDENTIFICATION-->S</UNS01>
        </UNS>

        <CNT>
            <CNT01>
                <CNT0101><!--6069: Control total type code qualifier-->2</CNT0101>
                <CNT0102><!--6066: Control total quantity-->4</CNT0102>

            </CNT01>
        </CNT>
        <UNT>
            <UNT01><!--0074: NUMBER OF SEGMENTS IN A MESSAGE-->22</UNT01>
            <UNT02><!--0062: MESSAGE REFERENCE NUMBER-->SSDD1</UNT02>

        </UNT>
    </ORDERS>
    <UNZ>
        <UNZ01><!--0036: INTERCHANGE CONTROL COUNT-->1</UNZ01>
        <UNZ02><!--0020: INTERCHANGE CONTROL REFERENCE-->6002</UNZ02>

    </UNZ>
</EDIFACT>
W3C Member
Print
Site Map | JDBC Drivers | ODBC Drivers | ADO.NET Data Providers | XML Editor | Mainframe Integration
Privacy | Trademarks | Terms of Use
Copyright © 2005-2008 DataDirect Technologies. All Rights Reserved | N. America: 800 876 3101 | World: 44 (0) 1753 218 930