Re: xml-into help

[tim <iseriesstuff@xxxxxxxxx>]

Thanks mike. I will try it out and let you know.

On 3/4/2016 2:17 PM, Mike Jones wrote:
> Hi Tim,
>
> I tend to take a SET based view on as much as possible, hence I'd try using
> the XMLTABLE function first to convert that XML into an SQL result set.
> Here's a great article on using that: DB2 For i XMLTABLE, Part 1: Convert
> XML to Tabular Data <http://www.itjungle.com/fhg/fhg061312-story02.html>
>
> I used that not long ago, and it worked great.  In my case, I had to pass
> the XML string data to the XMLPARSE function first, to effectively cast the
> XML string data as an XML value data type, before XMLTABLE() would accept
> it as input.
>
> Once that XML is in an SQL result set format, it then becomes simple to do
> whatever you want with it.  I anticipate going that route would result in a
> much smaller volume of code, given your final destination with this is the
> DB.  If the destination is a single table in the DB, then you likely can
> get the XML data into that table using a *single* SQL statement, that
> resembles this:
>
> insert into DESTINATION_TABLE
> select ... from XMLTABLE( XMLPARSE() )
>
> Going that route eliminates a lot of what I call "glue code".
>
> Mike
>
>
> > date: Thu, 3 Mar 2016 21:39:43 -0500
> > from: tim <iseriesstuff@xxxxxxxxx>
> > subject: xml-into help
> >
> > Im working on xml files for the first time. I have the following xml
> > snippet and need help with the syntax to read the info into our db.
> >
> > Any help would be apprecited.
> >
> >               <OrganizationDetail>
> >                 <FamilyTreeMemberRole>
> >                   <FamilyTreeMemberRoleText DNBCodeValue="12775">Global
> > Ultimate</FamilyTreeMemberRoleText>
> >                 </FamilyTreeMemberRole>
> >                 <FamilyTreeMemberRole>
> >                   <FamilyTreeMemberRoleText DNBCodeValue="12774">Domestic
> > Ultimate</FamilyTreeMemberRoleText>
> >                 </FamilyTreeMemberRole>
> >                 <FamilyTreeMemberRole>
> >                   <FamilyTreeMemberRoleText
> > DNBCodeValue="12773">Parent</FamilyTreeMemberRoleText>
> >                 </FamilyTreeMemberRole>
> >                 <FamilyTreeMemberRole>
> >                   <FamilyTreeMemberRoleText
> > DNBCodeValue="12771">Headquarters</FamilyTreeMemberRoleText>
> >                 </FamilyTreeMemberRole>
> > <StandaloneOrganizationIndicator>false</StandaloneOrganizationIndicator>
> > <ControlOwnershipDate>1985</ControlOwnershipDate>
> >                 <ControlOwnershipTypeText DNBCodeValue="9058">Privately
> > owned</ControlOwnershipTypeText>
> >               </OrganizationDetail>