SQLX — от БД прямо к XML и обратно

Ah please I need variables

Little help with variables brings WITH.

On DB2 simple like this

WITH 
vars(
DATETIMEMASK,               -- datetime mask
DATEMASK,                   -- date mask
TIMEMASK                    -- only time mask
) 
AS (VALUES(                 
'DD.MM.YYYY HH24:MI',     
'DD.MM.YYYY',               
'HH24:MI'))       
-- main select
SELECT XMLSERIALIZE(
        CONTENT
        XMLELEMENT(NAME "CustomerList",
          XMLAGG(
          XMLELEMENT(NAME "Customer",
          XMLATTRIBUTES(c.FIRST_NAME AS "Name",
TO_CHAR(c.LAST_ORDER,vars.DATEMASK) AS "LastOrder"
                                 )
                             )                            
                          )
                  )AS CLOB)
FROM vars,TMP.CUSTOMER c;

Oracle need SELECT something 

WITH 
vars(
DATETIMEMASK,           -- datetime mask
DATEMASK,               -- date mask
TIMEMASK                -- only time mask
) 
AS (SELECT                
'DD.MM.YYYY HH24:MI',     
'DD.MM.YYYY',               
'HH24:MI' 
FROM DUAL)                 
-- main select
SELECT XMLSERIALIZE(
        CONTENT
        XMLELEMENT(NAME "CustomerList",
         XMLAGG(
         XMLELEMENT(NAME "Customer",
         XMLATTRIBUTES(c.FIRST_NAME AS "Name",
TO_CHAR(c.LAST_ORDER,vars.DATEMASK) AS "LastOrder"
                                 )
                             )                            
                          )
                  )AS CLOB)
FROM vars,TMP.CUSTOMER c;

Output

<CustomerList>
    <Customer Name="Daniel" LastOrder="22.05.2013" />
    <Customer Name="Martin" LastOrder="22.05.2013" />
    <Customer Name="Eva"    LastOrder="22.05.2013" />
    <Customer Name="Alena"  LastOrder="22.05.2013" />
    <Customer Name="Oliver" LastOrder="22.05.2013" />
    <Customer Name="George" LastOrder="22.05.2013" />
    <Customer Name="Jamie"  LastOrder="22.05.2013" />
</CustomerList>

Expression as element name

Maybe you wonder how to make an element with name other than constant. On Oracle you can use EVALNAME() function:

SELECT XMLSERIALIZE(CONTENT 
        XMLELEMENT(NAME "PhoneBook",   
         XMLAGG(
          XMLELEMENT(EVALNAME(cust.FIRST_NAME),
           XMLATTRIBUTES(cust.LAST_NAME AS "LastName",
                                             cust.TEL)
                              )
                  ))AS VARCHAR(500))
FROM KYTYR.CUSTOMER cust;

And back to the Table

We have been dealing only with querying tables until now but what about inserts and updates? It is no problem with XMLTABLE which enable us to map the XML document to temporary table with XPath. Then it’s old plain SQL again.

Example of updates which works on both Oracle 11g and DB2 9.7

INSERT INTO TMP.DISCOUNT_CODE 
(SELECT discount_code, rate 
 FROM XMLTABLE('$doc/discount' PASSING 
                XMLPARSE(DOCUMENT 
'<discount>
 <code>H</code>
 <rate>5</rate>
 </discount>') as "doc"
       COLUMNS
          discount_code CHAR    PATH 'code',
          rate          DECIMAL PATH 'rate' ));

UPDATE TMP.DISCOUNT_CODE
SET (discount_code, rate) = (
    SELECT discount_code, rate
    FROM XMLTABLE('$doc/discount' PASSING 
                  XMLPARSE(DOCUMENT 
'<discount>
 <code>H</code>
 <rate>6</rate>
 </discount>') as "doc"
       COLUMNS
          discount_code CHAR    PATH 'code',
          rate          DECIMAL PATH 'rate' )
) WHERE discount_code = 'H';

Recreating of the sample table TMP.CUSTOMER, tested on DB2 9.7 and ORACLE 11g

CREATE TABLE TMP.CUSTOMER
(
ID int NOT NULL PRIMARY KEY,
FIRST_NAME varchar(255),
LAST_NAME  varchar(255),
LAST_ORDER TIMESTAMP,
TEL        DECIMAL(20)
);


INSERT INTO TMP.CUSTOMER(ID, FIRST_NAME, LAST_NAME,LAST_ORDER, TEL)
(SELECT id,first_name,last_name,CURRENT_TIMESTAMP,tel 
 FROM XMLTABLE('$doc/PhoneBook/Contact' PASSING 
                         XMLPARSE(DOCUMENT 
'<PhoneBook>
    <Contact id="1"        
             Name="Daniel" 
             Surname="Kec" 
             TEL="788255855"/>
    <Contact id="2"        
             Name="Martin" 
             Surname="Kec" 
             TEL="889665447"/>
    <Contact id="3"        
             Name="Eva"    
             Surname="Kec"    
             TEL="111222333"/>
    <Contact id="4" 
             Name="Alena"  
             Surname="Kec"    
             TEL="444555666"/>
    <Contact id="5" Name="Oliver" 
             Surname="Kec"    
             TEL="777888999"/>
    <Contact id="6" 
             Name="George" 
             Surname="Takei"  
             TEL="444882446"/>
    <Contact id="7" 
             Name="Jamie"  
             Surname="Oliver" 
             TEL="123456789"/>
</PhoneBook>') as "doc"
       COLUMNS
          id         INT         PATH '@id',
          first_name VARCHAR(50) PATH '@Name',
          last_name  VARCHAR(50) PATH '@Surname',  
          tel        DECIMAL(20) PATH '@TEL' ));

TMP.CUSTOMER