'***************************************************************************
' (c) Copyright IBM Corp. 2007 All rights reserved.
'
' The following sample of source code ("Sample") is owned by International
' Business Machines Corporation or one of its subsidiaries ("IBM") and is
' copyrighted and licensed, not sold. You may use, copy, modify, and
' distribute the Sample in any form without payment to IBM, for the purpose
' of assisting you in the development of your applications.
'
' The Sample code is provided to you on an "AS IS" basis, without warranty
' of any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS
' OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions
' do not allow for the exclusion or limitation of implied warranties, so the
' above limitations or exclusions may not apply to you. IBM shall not be
' liable for any damages you suffer as a result of using, copying, modifying
' or distributing the Sample, even if IBM has been advised of the
' possibility of such damages.
'
'***************************************************************************
'
' SOURCE FILE NAME: XmlXQuery.vb
'
' SAMPLE: How to run an nested XQuery
'
' XQUERY EXPRESSION USED
' FLWOR Expression
'
'
'***************************************************************************
'
' Building and Running the sample program
'
' 1. Compile the XmlXQuery.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldXMLapp XmlXQuery
'
' or compile XmlXQuery.vb with the makefile by entering the following at
' the command prompt:
'
' nmake XmlXQuery
'
' 2. Run the XmlXQuery program by entering the program name at the command
' prompt:
'
' XmlXQuery
'
'****************************************************************************
'
' For more information on the sample programs, see the README file.
'
' For information on developing applications, see the Application
' Development Guide.
'
' For information on using SQL statements, see the SQL Reference.
'
' For the latest information on programming, compiling, and running DB2
' applications, visit the DB2 Information Center at
' http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp/
'
'****************************************************************************/
Imports System.Collections
Imports System.Xml
Imports System.Text.RegularExpressions
Imports System
Imports System.Data
Imports System.IO
Imports IBM.Data.DB2
Imports Microsoft.VisualBasic
Public Class XmlXQuery
Inherits XML_Util
Public Shared Sub Main(ByVal args() As String)
' Declare a DB2Connection
Dim Run_Sample As XmlXQuery = New XmlXQuery
Try
Console.WriteLine()
Console.WriteLine("This sample will demonstrate how to run an nested XQuery XQUERY EXPRESSION USED FLWOR Expression" & vbCrLf)
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
If Run_Sample.ConnectDb(args) Then
' Do Some Sample Stuff
Run_Sample.PO_OrderByCity()
Run_Sample.CustomerOrderByProduct()
Run_Sample.PO_OrderByProvCityStreet()
Run_Sample.CustomerPO()
Else
Return
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Disconnect from the database
Try
Console.WriteLine(vbCrLf & " Disconnect from the database.")
Run_Sample.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
' Main
Public Function PO_OrderByCity() As Boolean
Dim query As String
Console.WriteLine(vbCrLf & _
"-------------------------------------------------------------" & vbCrLf & _
"RESTRUCTURE THE PURCHASEORDERS ACCORDING TO THE CITY...." & vbCrLf & vbCrLf)
Try
Dim cmd As DB2Command = dbconn.CreateCommand
Dim reader As DB2DataReader
query = vbCrLf & _
"XQUERY " & vbCrLf & _
" for" & vbCrLf & _
" $city in fn:distinct-values(" & vbCrLf & _
" db2-fn:xmlcolumn('CUSTOMER.INFO')" & vbCrLf & _
" /customerinfo/addr/city" & vbCrLf & _
" )" & vbCrLf & _
" return" & vbCrLf & _
" <city name='{$city}'>{" & vbCrLf & _
" for" & vbCrLf & _
" $cust in db2-fn:xmlcolumn('CUSTOMER.INFO')" & vbCrLf & _
" /customerinfo[addr/city=$city]" & vbCrLf & _
" let" & vbCrLf & _
" $po := db2-fn:sqlquery(" & vbCrLf & _
" """ & vbCrLf & _
" SELECT" & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"pos""""," & vbCrLf & _
" (" & vbCrLf & _
" XMLCONCAT(" & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"custid""""," & vbCrLf & _
" c.custid" & vbCrLf & _
" )," & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"order""""," & vbCrLf & _
" c.porder" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" FROM" & vbCrLf & _
" PURCHASEORDER AS c" & vbCrLf & _
" """ & vbCrLf & _
" )" & vbCrLf & _
" let" & vbCrLf & _
" $id := $cust/@Cid," & vbCrLf & _
" $order :=$po [custid=$id]/order" & vbCrLf & _
" return" & vbCrLf & _
" <customer id='{$id}'>" & vbCrLf & _
" {$cust/name}" & vbCrLf & _
" {$cust/Addr}" & vbCrLf & _
" {$order}" & vbCrLf & _
" </customer>" & vbCrLf & _
" }" & vbCrLf & _
" </city>" & vbCrLf & vbCrLf
Console.WriteLine(query)
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
While reader.Read
Console.WriteLine("|___________________________________________________")
Console.Write(display_xml_parsed_struct(reader.GetXmlReader(0), "| "))
End While
Catch e As Exception
Console.WriteLine(e.Message)
Return False
End Try
Console.WriteLine(vbCrLf)
Return True
End Function
' PO_OrderByCity
' This CustomerOrderByProduct function returns the purchaseorders product wise
Public Function CustomerOrderByProduct() As Boolean
Dim query As String
Console.WriteLine(vbCrLf & _
"-------------------------------------------------------------" & vbCrLf & _
"RESTRUCTURE THE PURCHASEORDER ACCORDING TO THE PRODUCT....." & vbCrLf & vbCrLf)
Try
Dim cmd As DB2Command = dbconn.CreateCommand
Dim reader As DB2DataReader
query = vbCrLf & _
"XQUERY " & vbCrLf & _
" for" & vbCrLf & _
" $city in fn:distinct-values(" & vbCrLf & _
" db2-fn:xmlcolumn('CUSTOMER.INFO')" & vbCrLf & _
" /customerinfo/addr/city" & vbCrLf & _
" )" & vbCrLf & _
" return" & vbCrLf & _
" <city name='{$city}'>{" & vbCrLf & _
" for" & vbCrLf & _
" $cust in db2-fn:xmlcolumn('CUSTOMER.INFO')" & vbCrLf & _
" /customerinfo[addr/city=$city]" & vbCrLf & _
" let" & vbCrLf & _
" $po := db2-fn:sqlquery(" & vbCrLf & _
" """ & vbCrLf & _
" SELECT" & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"pos""""," & vbCrLf & _
" (" & vbCrLf & _
" XMLCONCAT(" & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"custid""""," & vbCrLf & _
" c.custid" & vbCrLf & _
" )," & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"order""""," & vbCrLf & _
" c.porder" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" FROM" & vbCrLf & _
" PURCHASEORDER AS c" & vbCrLf & _
" """ & vbCrLf & _
" )" & vbCrLf & _
" let" & vbCrLf & _
" $id := $cust/@Cid," & vbCrLf & _
" $order := $po[custid=$id]/order" & vbCrLf & _
" return" & vbCrLf & _
" <customer id='{$id}'>" & vbCrLf & _
" {$cust/name}" & vbCrLf & _
" {$cust/Addr}" & vbCrLf & _
" {$order}" & vbCrLf & _
" </customer>" & vbCrLf & _
" }" & vbCrLf & _
" </city>" & vbCrLf & vbCrLf
Console.WriteLine(query)
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
While reader.Read
Console.WriteLine("|___________________________________________________")
Console.Write(display_xml_parsed_struct(reader.GetXmlReader(0), "| "))
End While
Catch e As Exception
Console.WriteLine(e.Message)
Return False
End Try
Console.WriteLine(vbCrLf)
Return True
End Function
' CustomerOrderByProduct
' This PO_OrderByProvCityStreet function returns the purchaseorder province, city and stree wise
Public Function PO_OrderByProvCityStreet() As Boolean
Dim query As String
Console.WriteLine(vbCrLf & _
"-------------------------------------------------------------" & vbCrLf & _
"RESTRUCTURE THE PURCHASEORDER DATA ACCORDING TO PROVIENCE, CITY AND STREET.." & vbCrLf)
Try
Dim cmd As DB2Command = dbconn.CreateCommand
Dim reader As DB2DataReader
query = vbCrLf & _
"XQUERY " & vbCrLf & _
" let" & vbCrLf & _
" $po := db2-fn:sqlquery(" & vbCrLf & _
" """ & vbCrLf & _
" SELECT" & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"pos""""," & vbCrLf & _
" (" & vbCrLf & _
" XMLCONCAT(" & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"custid""""," & vbCrLf & _
" c.custid" & vbCrLf & _
" )," & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"order""""," & vbCrLf & _
" c.porder" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" FROM" & vbCrLf & _
" PURCHASEORDER as c" & vbCrLf & _
" """ & vbCrLf & _
" )," & vbCrLf & _
" $addr:=db2-fn:xmlcolumn('CUSTOMER.INFO')" & vbCrLf & _
" /customerinfo/addr" & vbCrLf & _
" for" & vbCrLf & _
" $prov in distinct-values($addr/prov-state)" & vbCrLf & _
" return" & vbCrLf & _
" <province name='{$prov}'>{" & vbCrLf & _
" for" & vbCrLf & _
" $city in fn:distinct-values($addr[prov-state=$prov]/city)" & vbCrLf & _
" return" & vbCrLf & _
" <city name='{$city}'>{" & vbCrLf & _
" for" & vbCrLf & _
" $s in fn:distinct-values($addr/street)" & vbCrLf & _
" where" & vbCrLf & _
" $addr/city=$city" & vbCrLf & _
" return" & vbCrLf & _
" <street name='{$s}'>{" & vbCrLf & _
" for" & vbCrLf & _
" $info" & vbCrLf & _
" in" & vbCrLf & _
" $addr[prov-state=$prov" & vbCrLf & _
" and" & vbCrLf & _
" city=$city" & vbCrLf & _
" and" & vbCrLf & _
" street=$s]/.." & vbCrLf & _
" return" & vbCrLf & _
" <customer id='{$info/@Cid}'>{" & vbCrLf & _
" $info/name" & vbCrLf & _
" }" & vbCrLf & _
" {" & vbCrLf & _
" let" & vbCrLf & _
" $id := $info/@Cid," & vbCrLf & _
" $order := $po[custid=$id]/order" & vbCrLf & _
" return $order" & vbCrLf & _
" }" & vbCrLf & _
" </customer>" & vbCrLf & _
" }" & vbCrLf & _
" </street>" & vbCrLf & _
" }" & vbCrLf & _
" </city>" & vbCrLf & _
" }" & vbCrLf & _
" </province>" & vbCrLf & vbCrLf
Console.WriteLine(query)
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
While reader.Read
Console.WriteLine("|___________________________________________________")
Console.Write(display_xml_parsed_struct(reader.GetXmlReader(0), "| "))
End While
Catch e As Exception
Console.WriteLine(e.Message)
Return False
End Try
Console.WriteLine(vbCrLf)
Return True
End Function
' The CustomerPO function creates the purchaseorder XML document
Public Function CustomerPO() As Boolean
Dim query As String
Console.WriteLine(vbCrLf & _
"-------------------------------------------------------------" & vbCrLf & _
"COMBINE THE DATA FROM PRODUCT AND CUSTOMER TABLE TO CREATE A PURCHASEORDER.." & vbCrLf & vbCrLf)
Try
Dim cmd As DB2Command = dbconn.CreateCommand
Dim reader As DB2DataReader
query = vbCrLf & _
"XQUERY " & vbCrLf & _
" <PurchaseOrder>{" & vbCrLf & _
" for" & vbCrLf & _
" $ns1_customerinfo0" & vbCrLf & _
" in" & vbCrLf & _
" db2-fn:xmlcolumn('CUSTOMER.INFO')" & vbCrLf & _
" /customerinfo" & vbCrLf & _
" where" & vbCrLf & _
" ($ns1_customerinfo0/@Cid=1001)" & vbCrLf & _
" return" & vbCrLf & _
" <customer customerid='{ fn:string( $ns1_customerinfo0/@Cid)}'>{" & vbCrLf & _
" $ns1_customerinfo0/name" & vbCrLf & _
" }" & vbCrLf & _
" <address>{" & vbCrLf & _
" $ns1_customerinfo0/addr/street" & vbCrLf & _
" }" & vbCrLf & _
" {" & vbCrLf & _
" $ns1_customerinfo0/addr/city" & vbCrLf & _
" }" & vbCrLf & _
" {" & vbCrLf & _
" if($ns1_customerinfo0/addr/@country=""US"") then" & vbCrLf & _
" $ns1_customerinfo0/addr/prov-state" & vbCrLf & _
" else(" & vbCrLf & _
" )" & vbCrLf & _
" }" & vbCrLf & _
" {" & vbCrLf & _
" fn:concat(" & vbCrLf & _
" $ns1_customerinfo0/addr/pcode-zip/text()," & vbCrLf & _
" "",""," & vbCrLf & _
" fn:upper-case($ns1_customerinfo0/addr/@country)" & vbCrLf & _
" )" & vbCrLf & _
" }" & vbCrLf & _
" </address>" & vbCrLf & _
" </customer>" & vbCrLf & _
" }" & vbCrLf & _
" {" & vbCrLf & _
" for" & vbCrLf & _
" $ns2_product0 in db2-fn:xmlcolumn(" & vbCrLf & _
" '" & vbCrLf & _
" PRODUCT.DESCRIPTION" & vbCrLf & _
" '" & vbCrLf & _
" )/product" & vbCrLf & _
" where" & vbCrLf & _
" ($ns2_product0/@pid=""100-100-01"")" & vbCrLf & _
" return" & vbCrLf & _
" $ns2_product0" & vbCrLf & _
" }" & vbCrLf & _
" </PurchaseOrder>" & vbCrLf & vbCrLf
Console.WriteLine(query)
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
While reader.Read
Console.WriteLine("|___________________________________________________")
Console.Write(display_xml_parsed_struct(reader.GetXmlReader(0), "| "))
End While
Catch e As Exception
Console.WriteLine(e.Message)
Return False
End Try
Console.WriteLine(vbCrLf)
Return True
End Function
End Class
' DbDsetCn