Creating XML from SQL

[DeadManWalks]

I have the following SQL with 2 fields.

select XMLSERIALIZE(
XMLELEMENT(NAME "Labels",
XMLAGG(xmlrow(store as "Store", format as "Format" OPTION ROW "Labels"))) 
as CLOB(1M) INCLUDING XMLDECLARATION) as XML_DOCUMENT
from (Select * from lbl801sl order by store) Product

The results are

com.ibm.as400.access.AS400JDBCClobLocator@cd32f4

instead of the XML document that the examples give. Any random thoughts?

[DeadManWalks]

Re: Creating XML from SQL

I see my issue. I had to direct the results to a ifs file use qsh. its rather uggly. I would not suggest it.

[B.Hauser]

Re: Creating XML from SQL

Are you using STRSQL for executing your SQL statement?
If so try IBM i Navigator' Run an SQL Script or the SQL Script included in the ACS tool.

Birgitta

[DeadManWalks]

Re: Creating XML from SQL

I have this which runs in the "Run sql scripts" ibm program.

                                   
Select                                                              
XmlElement(NAME "PO",                                               
 XmlForest(PONUMB as "PONumber", INUMBR as "Sku"),                  
 XmlElement(NAME "Qty",                                             
  XmlForest(Pomum as "Uom", PomCst as "Cost", PomQty as "Quantity") 
                 )                                                  
                )                                                   
 from PomDtl                                                        
where PoNumb = 1000000058;

This gives me pretty xml data.

I have tried in SQLRPGLE to get this to insert into a db2 table. I keep on getting "value for column not compatible"

If anyone has been successful in getting xml from db2 into a IFS file I would love to know.

[Vectorspace]

Re: Creating XML from SQL

I would be interested to know how to get XML from a db2 file into a db2 flat file (i.e. a file with a single really long character field), as opposed to an IFS file. For some reason we don't make use of the IFS here .

[B.Hauser]

Re: Creating XML from SQL

In the following example an XML file is generated and directly written into the IFS:

                                                                   
   DCL-S MyXMLDoc SQLTYPE(XML_CLOB_File);                                    
   //------------------------------------------------------------------------------------------------
      Exec SQL   Set Option  Commit=*None, DatFmt=*ISO, TimFmt=*ISO,            
                          Naming=*SYS,  CloSQLCsr=*EndActGrp                 
                                                                             
     Clear MyXMLDoc;                                                         
     MyXMLDoc_Name = '/home/Hauser/MyXMLDoc.xml';                            
     MyXMLDoc_NL   = %Len(%Trim(MyXMLDoc_Name));                             
     MyXMLDoc_FO   = SQFOVR;                           //Replace if exists   
                                                                             
     Exec SQL                                                                
         Select XmlDocument                                                  
                  (xmlgroup(EmployeeNo as "PersNo",                          
                            Trim(Trim (FirstName) concat ' ' concat          
                                 Trim(Name)) as "PersName",                  
                            Address as "Street",                             
                            ZipCode as "ZipCode",                            
                            City as "City"                                   
                            Order By City Desc, Name                         
                            Option Row "Employee"                            
                            Root "Staff"))                                   
           into :MyXMLDoc                                                    
           From LobStaff;                                                    
                                                                             
     If SQLCODE < *Zeros;                                                    
        Dsply 'Error occured';                                               
     else;                                                                   
        Dsply 'XML Document generated';                                      
     EndIf;                                                                  
                                                                             
   *InLR       = *On;

Birgitta

[DeadManWalks]

Re: Creating XML from SQL

tyvm. I will give this a whirl.

[DeadManWalks]

Re: Creating XML from SQL

The example is great. Is there a way to insert CrLf at different breaks. It would make the document easier to read in text form.

[DeadManWalks]

Re: Creating XML from SQL

Better yet, can you point me to the xml document with an explanation of all the formats for the functions. I have looked at information center for v7.1 but no luck.

Ignore this I found it. Now I just need an example of making attributes instead of all separate elements.

[DeadManWalks]

Re: Creating XML from SQL

Apparently you can not use attributes inside an xmldocument.

Error - XML attribute is not allowed in XMLDOCUMENT!

Do you have a small sample of how i can do elements and attributes?

[DeadManWalks]

Re: Creating XML from SQL

I have looked at the information center and it says i should be able to do this, but it does not like me using "as attributes" with the other option parms.

         Select XmlDocument                                                  
                  (xmlgroup(AaNum  as "Vendor",                          
                            Aaname as "Name"
                            Order By AaNum                         
                            Option 
                            as attributes
                            Row "party_profile"                            
                            Root "document"
                           ) 
                           -- end group
                  ) -- end document                                  
           --into :MyXMLDoc                                                    
           From ApAddr                                                    
           Where aatype = '1'

any clue as to how i am reading the spec wrong?

I know , enough questions already.

[B.Hauser]

Re: Creating XML from SQL

syntax is:

Select XmlDocument                                                  
                  (xmlgroup(AaNum  as "Vendor",                          
                            Aaname as "Name"
                            Order By AaNum                         
                            Option 
                            Row "party_profile"                            
                            Root "document"
                          [COLOR="#FF0000"][B] as attributes[/B][/COLOR]) 
                           -- end group
                  ) -- end document                                  
           --into :MyXMLDoc                                                    
           From ApAddr                                                    
           Where aatype = '1'

Birgitta

[DeadManWalks]

Re: Creating XML from SQL

FYI, i am using SQL scripts program and it will not allow me to use Row and Root with as attributes. It just throws a generic SQL0901. I can use just as attributes or just the other two.

I found your big example of a CTE xml and am working through it to model it for me. I'll let you see what I have when it works.

[Michael Jones]

Re: Creating XML from SQL

FYI...SQL0901 means you've likely hit an IBM bug. In my experience, SQL0901s occur when your SQL passes syntax check, but fails at runtime, typically accompanied with a low level MCH* error in the job log. In those cases, I've been very successful at tweaking or restructuring the code to work around the error.

[DeadManWalks]

Re: Creating XML from SQL

I am posting 2 small examples for the next poor sap.

This one is one table but shows how to use a XmlConcat to make a rolling sub element;

           Select xmldocument( 
           XmlElement(NAME "document",
           XMLNAMESPACES(DEFAULT 'partyschema.xsd',
              'http://www.w3.org/2001/XMLSchema-instance' as"xsi"),            
           XmlElement(NAME "party_profile",
           xmlagg(
            XmlElement(NAME "party",
           	XmlAttributes(Asnum as "Vendor", AsName as "Name",
           	AsType as "Type"),
           	  XmlConCat(
           	XmlElement(Name "address",
           	XmlAttributes('1' as "Type", AsClrk as "Clerk")
           	),  -- Part 1 of concat
           	XmlElement(NAME "address",
           	XmlAttributes('2' as "Type", AsBuyr as "Buyer")
           	) -- part 2 of concat
           	  ) -- End Concat
                ) -- End Element party
                ) -- End Agg
                ) -- End Element party_profile
                ) -- End Element document
                ) -- End Document
           --into :MyXMLDoc
           from apsupp
           where asnum = 3614

This example shows how to use a sub select to get the sub element "address";

           Select xmldocument( 
           XmlElement(NAME "document",
           XMLNAMESPACES(DEFAULT 'partyschema.xsd',
              'http://www.w3.org/2001/XMLSchema-instance' as"xsi"),            
           XmlElement(NAME "party_profile",
           xmlagg(
            XmlElement(NAME "party",
           	XmlAttributes(a.Asnum as "Vendor", a.AsName as "Name",
           	AsType as "Type"),

            (Select xmlagg(           
           	XmlElement(Name "address",
           	XmlAttributes(b.AaType as "Type", b.aaadd1 as "Street")
            ) -- end Element Address            
            ) -- end agg            
           	from apaddr b
           	where b.aanum = 3614 and B.aatype <> '1'
            ) -- end Select
                ) -- End Element party
                ) -- End Agg
                ) -- End Element party_profile
                ) -- End Element document
                ) -- End Document
           --into :MyXMLDoc
           from apsupp A
           where asnum = 3614

I have to work with the CTE or get an inline view to work to remove my inner vendor number = to. But this gets me out of the woods and hopefully will help someone else.

Love DMW.