Hello all,
I ran into an interesting SQL/XML question yesterday. A customer wants
to make a part of the relational data available through a webservice
as XML. This seemed like the perfect opportunity to use the DB2 XML-
capabilities. The following test scenario will make things easier to
explain:
drop table test.customer;
create table test.customer
(
id integer not null,
name varchar(255) not null,
email varchar(255),
customer_type char(1),
contact_of_cust omer_id integer, -- points to a custom from whom this
custom is a contact
primary key(id)
);
insert into test.customer(i d, name, email, customer_type,
contact_of_cust omer_id) values (1, 'name1', '1@1.com', 'A', null), (2,
'name2', '2@2.com', 'A', null), (3, 'name3', '2@2.com', 'B', 2), (4,
'name4', '4@4.com', 'C', 2);
The database structure allows for customers to have contacts (which in
turn are customers as well, etc). In practice, the application will
allow only one level of contacts, so no recursion is required. As you
can see, customer 1 and 2 are of the required type 'A', 1 having no
contacts, 2 having customers 3 and 4 as contact.
The XML should contain a list of customers (including their details),
and each one that has contacts should have them listed as child nodes.
This gives the following (the output format is fixed, I can't change
it):
<customers>
<customer>
<customerId>1 </customerId>
<customerInfo >
<customerName>n ame1</customerName>
<customerEmail> 1@1.com</customerEmail>
</customerInfo>
</customer>
<customer>
<customerId>2 </customerId>
<customerInfo >
<customerName>n ame2</customerName>
<customerEmail> 2@2.com</customerEmail>
</customerInfo>
<customerContac t>
<customerId>3 </customerId>
<customerInfo >
<customerName>n ame3</customerName>
<customerEmail> 2@2.com</customerEmail>
</customerInfo>
</customerContact >
<customerContac t>
<customerId>4 </customerId>
<customerInfo >
<customerName>n ame4</customerName>
<customerEmail> 4@4.com</customerEmail>
</customerInfo>
</customerContact >
</customer>
</customers>
I've created the following xquery that returns one record containing
the neccesary XML:
XQUERY
let $customers := db2-fn:sqlquery(
"
select
XMLELEMENT
(
NAME ""customer" ",
XMLELEMENT
(
NAME ""customerI d"",
cust.id
),
XMLELEMENT
(
NAME ""customerInfo" ",
XMLFOREST
(
cust.name as ""customerName" ",
cust.email as ""customerEmail ""
)
),
XMLAGG
(
XMLELEMENT
(
NAME ""customerConta ct"",
XMLELEMENT
(
NAME ""customerI d"",
cont.id
OPTION NULL ON NULL
),
XMLELEMENT
(
NAME ""customerInfo" ",
XMLFOREST
(
cont.name as ""customerName" ",
cont.email as ""customerEmail ""
)
OPTION NULL ON NULL
)
OPTION NULL ON NULL
)
)
)
from test.customer cust
left outer join test.customer cont on cont.contact_of _customer_id =
cust.id
where cust.customer_t ype = 'A'
group by cust.id, cust.name, cust.email
")
return
<customers>{$cu stomers}</customers>;
It selfjoins customer (cust and cont) and groups by the fields in
cust. The XMLAGG function does the rest. This was based on the example
used in the SQL Reference Vol.1: XMLELEMENT, regarding the
departments.
The query functions correctly. Notice however the GROUP BY-statement
at the end of it. The real table contains much more data, including
data from other tables as well. With this query, I have to GROUP BY on
every column I want to include in customer. This seems unnecessary and
wasteful. I am sure that there is a better way to write this one.
XQuery guru's (and even lesser gods), I would like to ask you to take
your best shot at it.
Many thanks in advance,
Frederik 0 1434 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Paul Morrow |
last post by:
I'm hoping that someone can explain why I get the following exception.
When I execute the code...
######################################
class Parent(object):
class Foo(object):
baz = 'hello from Parent.Foo'
class Child(Parent):
#Foo.baz = 'hello from Child.Foo'
|
by: DelphiBlue |
last post by:
I have a Nested Datagrid that is using a data relations to tie the parent
child datagrids together. All is working well with the display but I am
having some issues trying to sort the child datagrid.
HTML
Datagrid1
TemplateColumn
Table
Header information
Detail Information
|
by: Robert W. |
last post by:
I've almost completed building a Model-View-Controller but have run into a
snag. When an event is fired on a form control I want to automatically
updated the "connnected" property in the Model. This works fine if all of
the properties are at the top (root level) of the model but I'd like to keep
them in nested classes to organize them better.
So, for example, part of my data model looks like this (simplified) :
public class MainClass
|
by: miked |
last post by:
I am architecting in a read only class for use in mapping data to a business
object. The object makes strong use of nested classes and their ability to
access protected fields. The downside is when a nested class inherits from
it’s parent class you get this infinite class chain in intellisense when
consuming the class. To get around this I created two child classes Reader
and Writer which require a base Person object.
When consuming...
|
by: BMeyer |
last post by:
I have been losing my mind trying to parse an XML document (with nested child
elements, not all of which appear in each parent node) into a DataGrid
object. What I want to do is "flatten" the XML document into a text
document with a single row for each parent node (that has all of the values
from all of the child nodes for that row)
The DataView within VS 2005 IDE displays my 15 or so child tables - and
knows that some parent rows...
| |
by: masri999 |
last post by:
Hello,
I want to share my experiences about using insert into exec which may
help others .
Using SQL Server 2000, SP3 .
Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicit
transactions .
I have defined a # table in Parent SP and calling a Child SP like this
|
by: Adam Knight |
last post by:
Hi All,
I have a repeater control nested inside another repeater control.
The nested (child) repeater control, is bound to an object data source.
My problem is, i need to send a data item value from the 'item' of parent
control
as a parameter to the object datasource of the child repeater control.
Any thoughts on how to best do this? Declaritively?
|
by: rupalirane07 |
last post by:
Both grids displays fine. But the problem is only parent datagrid sorting works fine but when i clik on child datagrid for sorting it gives me error:
NullReferenceException error
Any help........pls urgent
=========================================================
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm3.aspx.vb" Inherits="TestDatagrids.WebForm3"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">...
|
by: GISmatters |
last post by:
I have unbound checkboxes in a nested gridview to allow multi-selection of "child" rows. For context, the parent gridview rows are for large "reports", the child rows are for various specific files comprising each report. I want the user to be able to select an arbitrary collection of report files and have them emailed by clicking an "Email selected files" button.
Everything displays properly, including the checkboxes for each child row (each...
|
by: omgdork |
last post by:
Hi!
I'm having trouble on how to proceed with inserting data that are in parent and child gridviews. I have a gridview with nested gridviews that looks like this:
http://img17.imageshack.us/img17/7442/nestedgridview1.jpg
I could already display the gridview but the problem is storing the data.
All the gridviews (parent and children) have pagers and sorting. The child gridviews are in UpdatePanels. All the checkboxes are checked by...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |