Xquery in DB2 - Page 2

[dilasing]

For this you can use XQuery. A powerful aspect of XQuery is its ability to transform XML output from one form of XML into another. For example, you can use XQuery to retrieve all or part of your stored XML documents and convert the output into HTML for easy display in a Web browser.

Consider below mentioned query which retrieves the addresses of our clients, sorts the results by zip code, and converts the output into XML elements that are part of an unordered HTML list:

xquery
<ul> {
for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/Address
order by $y/zip
return <li>{$y}</li>
} </ul>

The query begins simply enough with the xquery keyword to indicate to the DB2 parser that XQuery is being used as the primary language. The second line causes the HTML markup for an unordered list (<ul>) to be included in the results. It also introduces a curly bracket,Curly brackets instruct DB2 to evaluate and process the enclosed expression rather than treat it as a literal string.

The output will appear similar to:

<ul>
<li>
<Address>
<street>9407 Los Gatos Blvd.</street>
<city>Los Gatos</city>
<state>CA</state>
<zip>95032</zip>
</Address>
</li>
<li>
<Address>
<street>4209 El Camino Real</street>
<city>Mountain View</city>
<state>CA</state>
<zip>95033</zip>
</Address>
</li>
. . .
</ul>

[ssahil11]

Can we use conditional expressions in XQuery?

[dilasing]

Yes, we can use conditional expressions in XQuery. XQuery's ability to transform XML output can be combined with its built-in support for conditional logic to reduce the complexity of application code. We can look at a simple example. The "items" table includes an XML column containing comments customers have made about products. The comments xml document can be like following document:

<Comments>
	<Comment>
		<CommentID>133</CommentID>
		<ProductID>3926</ProductID>
		<CustomerID>8877</CustomerID>
	<Message>Heels on shoes wear out too quickly.</Message>
		<ResponseRequested>No</ResponseRequested>
	</Comment>
	<Comment>
		<CommentID>514</CommentID>
		<ProductID>3926</ProductID>
		<CustomerID>3227</CustomerID>
	<Message>Where can I find a supplier in San Jose?</Message>
		<ResponseRequested>Yes</ResponseRequested>
	</Comment>

</comments>

For customers who have requested a response to their comments, you may want to create new "action" elements containing the product ID, customer ID, and message so you can route this information to the appropriate person for handling. For Comments that don't require a response create an "info" element with just the product ID and message.

This is how you can use an XQuery if-then-else expression to accomplish this task:

xquery

for $y in db2-fn:xmlcolumn('ITEMS.COMMENTS')/Comments/Comment 
return ( 
	if ($y/ResponseRequested = 'Yes') 
		then <action>
			{$y/ProductID, 
			 $y/CustomerID, 
			 $y/Message}
		      </action>
		else ( <info>
			{$y/ProductID, 
			 $y/Message}
			</info>
		)
)