Hi all,
I've to create an xml, I made all via sql, but when I tried to validate my output Xml, this failed, because I didn't have all the group of tag requested by the schema.
To resolve this I was thinking to add group to my table when I made the group by.
If someone have a better idea please suggest it to me.
Here's an example an what I want:
Imaging that my schema want 9 kind of job for example:
but generating it from the table employee I din't find the ADMIN category:
So I think to make a model table with all the category requested by the schema and than a union with my employee table, a better complicated because I had to make it a lot of times...
but this is the idea:
Does anyone have a better idea?
Many thanks.
Bye
I've to create an xml, I made all via sql, but when I tried to validate my output Xml, this failed, because I didn't have all the group of tag requested by the schema.
To resolve this I was thinking to add group to my table when I made the group by.
If someone have a better idea please suggest it to me.
Here's an example an what I want:
Imaging that my schema want 9 kind of job for example:
Code:
<employee> <job>ANALYST</job> <numberofemployee>6</numberofemployee> <job>DESIGNER</job> <numberofemployee>20</numberofemployee> <job>CLERK</job> <numberofemployee>16</numberofemployee> <job>OPERATOR</job> <numberofemployee>12</numberofemployee> <job>PRES</job> <numberofemployee>2</numberofemployee> <job>MANAGER</job> <numberofemployee>14</numberofemployee> <job>SALESREP</job> <numberofemployee>4</numberofemployee> <job>ADMIN</job> <numberofemployee>0</numberofemployee> <job>FIELDREP</job> <numberofemployee>10</numberofemployee> </employee>
Code:
with job(jobxml) as ( Select Xmlconcat(XmlElement(Name "job", trim(Job)), XmlElement(Name "numberofemployee", count(*))) From employee group by job) Select xmlelement(Name "employee", xmlagg(jobxml)) from job; <employee> <job>MANAGER</job> <numberofemployee>7</numberofemployee> <job>DESIGNER</job> <numberofemployee>10</numberofemployee> <job>OPERATOR</job> <numberofemployee>6</numberofemployee> <job>FIELDREP</job> <numberofemployee>5</numberofemployee> <job>PRES</job> <numberofemployee>1</numberofemployee> <job>ANALYST</job> <numberofemployee>3</numberofemployee> <job>CLERK</job> <numberofemployee>8</numberofemployee> <job>SALESREP</job> <numberofemployee>2</numberofemployee> </employee>
So I think to make a model table with all the category requested by the schema and than a union with my employee table, a better complicated because I had to make it a lot of times...
but this is the idea:
Code:
Declare global temporary table modello as ( Select Job, Count(*) as conta From Sample/Employee Group By Job ) with data; select *From modello; insert into modello values('ADMIN', 0); With job1 (job, conta) As ( Select job, sum(conta) From Modello group by job Union All Select Job, Count(*) As Conta From Sample/Employee Group By Job ), job2 (jobxml) as ( Select Xmlconcat(XmlElement(Name "job", trim(Job)), XmlElement(Name "numberofemployee", Sum(conta))) From job1 group by job) Select xmlelement(Name "employee", xmlagg(jobxml)) from job2 ;
Many thanks.
Bye
Comment