![]() f9031852-e536-4c1e-b829-fa673ce3b8f4 Sam IT India Local Singapore Permanent India Temporary Malaysia 1 Andy Sales 2 Bill IT 3 Candy Marketing 4 Dude Sales 1000 f9031852-e536-4c1e-b829-fa673ce3b8f4 This will help to visualize and understand the data tags generated and how the corresponding XML functions are working. Inner join ( select empno, uuid_generate_v4() uuid from emp ) tempīefore we start on the explanation of the xml functions used in the SELECT clause, let us first look at the resulting output. , VERSION '1.0" encoding="utf-8', STANDALONE YES)įrom emp emp -DRIVING TABLE left outer join dept dept Xmlelement( name "Skill", referrals.skill) Xmlelement( name "Name", referrals.name), Xmlelement( name "ReferralId", referrals.id), ( select xmlagg( -referrals DETAIL *Repeating group xmlelement( name "Referrals", Where emp.empno = empaddress.empno) empaddress Xmlelement( name "Country", empaddress.address)įrom ( select empaddress.*, row_number() over( order by empaddress.type ) seqnum Xmlelement( name "Type", empaddress.type), ( select xmlagg( -address DETAIL *Repeating group xmlelement( name address, Xmlelement( name "Department", -department xmlattributes(ptno as "DepartmentNumber"), Xmlelement( name "DocumentId", temp.uuid), Xmlelement( name "Employee", -employee HEADER xmlattributes(emp.empno as "EmployeeNumber"), Select uuid_generate_v4() Query Statementįirstly let us check the SELECT statement used to generate the XML data tags. In this example, we will generate UUID for each xml document for an employee for that we will use the below PostgreSql extension. Also another table which keeps a track of referrals posted by the employees. So we have created four tables along with sample data. Insert into referrals (empno, name,skill) values( 3, 'Zombie', 'Marketing') select * from referrals ġ 1 "Andy" "Sales" 2 1 "Bill" "IT" 3 1 "Candy" "Marketing" 4 1 "Dude" "Sales" 5 2 "Jack" "HR" 6 2 "Kane" "Sales" 7 3 "Zombie" "Marketing" Insert into referrals (empno, name,skill) values( 2, 'Kane', 'Sales') Insert into referrals (empno, name,skill) values( 2, 'Jack', 'HR') Insert into referrals (empno, name,skill) values( 1, 'Dude', 'Sales') Insert into referrals (empno, name,skill) values( 1, 'Candy', 'Marketing') Insert into referrals (empno, name,skill) values( 1, 'Bill', 'IT') Insert into referrals (empno, name,skill) values( 1, 'Andy', 'Sales') Referrals Table drop table if exists referrals Insert into empaddress (empno, type,address) values( 2, 'Permanent', 'Singapore') select * from empaddress ġ 1 "Local" "Singapore" 2 1 "Permanent" "India" 3 1 "Temporary" "Malaysia" 4 2 "Permanent" "Singapore" Insert into empaddress (empno, type,address) values( 1, 'Temporary', 'Malaysia') Insert into empaddress (empno, type,address) values( 1, 'Permanent', 'India') Insert into empaddress (empno, type,address) values( 1, 'Local', 'Singapore') Insert into dept (deptno,dname,loc) values( 20, 'SALES', 'Singapore') select * from dept ġ0 "IT" "India" 20 "SALES" "Singapore"Īddress Table drop table if exists empaddress Insert into dept (deptno,dname,loc) values( 10, 'IT', 'India') Insert into emp (ename,deptno,sal) values( 'Alen', 10, 3000) select * from emp ġ "Sam" 10 1000 2 "Rock" 20 2000 3 "Alen" 10 3000ĭepartment Table drop table if exists dept Insert into emp (ename,deptno,sal) values( 'Rock', 20, 2000) Insert into emp (ename,deptno,sal) values( 'Sam', 10, 1000) Let's create a few sample database tables in PostgreSQL for our use case implementation. xmlroot - Concatenate the root/header of the document with the generated XML document.xmlagg - Generate a block of aggregated XML block Tags, basically repeating groups / occurs many times.xmlelement - Generate XML Element Tags- whether simple or complex.xmlattributes - Generate XML Attribute Tags.We are mainly going to using four types of XML database functions, to generate any kind of complex xml you can think of.īelow are the functions and their description. You may leverage an ETL tool to generate the corresponding data files, else use database specific file writer utility. Note that writing data to the file is not in scope of this article. ![]() Next we will write a simple SELECT statement (based on KISS design principle) to generate a complex XML document tree. To start with the use case, let us first build the sample tables in our database schema. In general any XML document have a document root, simple elements, complex elements, attributes & repeating element group. On a high-level, almost all databases have same kind of functions to generate xml from database tables, although the syntax may vary.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |