Post
Ask Question

XML as a Parameter in SQL Query

We have all been creating SQL queries with parameters, maybe in different software like SQL Server, PL/SQL, DB2, etc.

Today, we are going to see how to handle XML Data in a SQL Query. I am going to use PL/SQL for it as I have that handy but I am pretty sure it can be done in SQL Server as well, obviously with a different format.
 
So let's begin.

Our XML format is as below, 
  1. <parms>  
  2.   <parm>  
  3.     <FIRSTNAME>Vipul</FIRSTNAME>  
  4.     <LASTNAME>Malhotra</LASTNAME>  
  5. <AGE>25</AGE>  
  6. <GENDER>M</GENDER>  
  7.   </parm>  
  8.  <parm>  
  9.     <FIRSTNAME>Gaurav</FIRSTNAME>  
  10.     <LASTNAME>Malhotra</LASTNAME>  
  11. <AGE>33</AGE>  
  12. <GENDER>M</GENDER>  
  13.   </parm>  
  14. </parms>  
Now, let's start writing the query in the below steps.
 
Step 1

First we need to make sure that the text above is considered as XML and nothing else. We achieve this as: 
  1. WITH t as (select XMLTYPE('  
  2. <parms>  
  3.   <parm>  
  4.     <FIRSTNAME>Vipul</FIRSTNAME>  
  5.     <LASTNAME>Malhotra</LASTNAME>  
  6. <AGE>25</AGE>  
  7. <GENDER>M</GENDER>  
  8.   </parm>  
  9.  <parm>  
  10.     <FIRSTNAME>Gaurav</FIRSTNAME>  
  11.     <LASTNAME>Malhotra</LASTNAME>  
  12. <AGE>33</AGE>  
  13. <GENDER>M</GENDER>  
  14.   </parm>  
  15. </parms>') as xml from dual)  
Please notice that we have used it inside the " WITH t as" only because we want to further use the xml data with Alias "t". For PL/Sql the "dual" is an inbuild function that you can read about more from the web. 
 
Step 2

Now, we have to select the data from the Alias table "t" as described above and read the data from each node by reading though it. In order to do that we do mentioned the starting of the node. This is done as: 
  1. select x.age,x.firstname,x.lastname,x.gender  
  2. from t  
  3.     ,XMLTABLE('/parms/parm'  
  4.               PASSING t.xml  
  5.               COLUMNS age NUMBER PATH '/parm/AGE'  
  6.               , firstname VARCHAR2(50)   PATH '/parm/FIRSTNAME'  
  7.               ,lastname VARCHAR(50)      PATH '/parm/LASTNAME'  
  8.               ,gender CHAR(1) PATH '/parm/GENDER'  
  9.              ) x  
  10.      
  11.                
In the sql above, we have mentioned the base of the node by the line and then we padded the earlier declared table "t" and read further nodes from it by iterating through the nodes like "/parm/AGE" for age , '/parm/FIRSTNAME' for FirstName, '/parm/LASTNAME' for LastName and '/parm/GENDER' for Gender. 
 
The final code comes out to be as below, 
  1. WITH t as (select XMLTYPE('  
  2. <parms>  
  3.   <parm>  
  4.     <FIRSTNAME>Vipul</FIRSTNAME>  
  5.     <LASTNAME>Malhotra</LASTNAME>  
  6. <AGE>25</AGE>  
  7. <GENDER>M</GENDER>  
  8.   </parm>  
  9.  <parm>  
  10.     <FIRSTNAME>Gaurav</FIRSTNAME>  
  11.     <LASTNAME>Malhotra</LASTNAME>  
  12. <AGE>33</AGE>  
  13. <GENDER>M</GENDER>  
  14.   </parm>  
  15. </parms>') as xml from dual)  
  16. -- END OF TEST DATA  
  17. select x.age,x.firstname,x.lastname,x.gender  
  18. from t  
  19.     ,XMLTABLE('/parms/parm'  
  20.     PASSING t.xml  
  21.         COLUMNS age NUMBER PATH '/parm/AGE'  
  22.         , firstname VARCHAR2(50)   PATH '/parm/FIRSTNAME'  
  23.         ,lastname VARCHAR(50)      PATH '/parm/LASTNAME'  
  24.         ,gender CHAR(1) PATH '/parm/GENDER'  
  25.     ) x               
The output generated by the above query is as below,