Goldengate reading XML data from BLOB column and inserting record into target table

Given below is a simple workaround for reading XML  from a blob column and parsing XML  and inserting it as a record into the target table.Usage of C userexit to accomplish the same is mentioned later in this post.

Extract group e_xml

extract e_xml

userid source, password ggs

rmthost rukh, mgrport 8809

rmttrail /opt/oracle/gg/rtrails/xm

table source.xml_blob, &

sqlexec (id lookup, query " &

select x.* &

from xml_blob t, &

xmltable( &

'/Zipcodes/mappings' &

passing xmltype(t.doc, nls_charset_id('CHAR_CS')) &

columns &

state_abbreviation varchar2(2)  path 'STATE_ABBREVIATION', &

zipcode            number(5)    path 'ZIPCODE', &

zip_code_extn      number(4)    path 'ZIP_CODE_EXTN', &

city               varchar2(50) path 'CITY' &

) x", noparams),tokens ( state_abbreviation = lookup.state_abbreviation, &

zipcode = lookup.zipcode, zip_code_extn = lookup.zip_code_extn, &

city = lookup.city);

Replicat group  r_xml

replicat r_xml

sourcedefs /opt/oracle/gg/dirdef/xmlblob.def

userid target, password ggs

discardfile /opt/oracle/gg/r_xml.dsc, purge

map source.xml_blob, target target.zip_codes, colmap (usedefaults, state_abbrevi

ation = @token("state_abbreviation"), zipcode = @token("zipcode"), zip_code_extn

 = @token("zip_code_extn"), city = @token("city"));

Insert statement to insert XML data

insert into xml_blob values(2,

utl_raw.cast_to_raw(

'

CA

94301

Palo Alto

')

);

Target table zip_codes 

create table zip_codes

(doc_id   number, 

state_abbreviation varchar2(2),

zipcode            varchar2(5),

zip_code_extn      varchar2(4),

city               varchar2(50),

constraint doc_id_zc_pk primary key(doc_id)

);

Userexit to read XML
There are some Userexit examples in the goldengate installation.They can be found in
Userexit directory inside the goldengate installation.There is one example for LOB in "goldengate\UserExitExamples\ExitDemo_lobs" which I have used for this purpose .I have combined this with a SAX parser example from the Oracle XML Developer Kit to create a DLL that could read XML from a BLOB column and parse it and insert  the node element values into a table as record.




********SAX Parser to parse the XML data ********

static XMLSAX_START_DOC_F(startDocument, ctx)
{
return 0;
}

static XMLSAX_END_DOC_F(endDocument, ctx)
{
    return 0;
}

static XMLSAX_START_ELEM_F(startElement, ctx, name, attrs)
{
    ((saxctx *) ctx)->elem = name;
    return 0;
}

static XMLSAX_END_ELEM_F(endElement, ctx, name)
{
    ((saxctx *) ctx)->elem = (oratext *) "";
    return 0;
}

static XMLSAX_CHARACTERS_F(characters, ctx, ch, len)
{
saxctx *sc = (saxctx *) ctx;

append(&head,len,sc->elem,ch);

    return 0;
}

static xmlsaxcb saxcb = {
    startDocument,
    endDocument,
    startElement,
    endElement,
    characters
};



int parse_xml(char *addr_buf,int buf_len )
{
    xmlctx     *xctx;
    saxctx      sc;
    xmlerr      ecode;

  
if (!(xctx = XmlCreate(&ecode, (oratext *) "saxsample_xctx", NULL)))
    {
        output_msg("Failed to create XML context, error %u\n", (unsigned) ecode);
        return 1;
    }


    if (ecode = XmlLoadSax(xctx, &saxcb, &sc, "buffer",  addr_buf, "buffer_length", buf_len,
  "validate", TRUE, "discard_whitespace", TRUE, NULL))
return 1;

    XmlDestroy(xctx);

    return 0;
}

/* -------Linked list to copy all parsed XML data---------------*/


void append(struct node **q,size_t lengt,oratext *elem,oratext *value)
{   struct node *first,*next;
first = *q;

if(*q==NULL)
{   first = malloc(sizeof(*first));
 first->leng=lengt;
 first->elemname=malloc(strlen(elem)+1);
 strcpy(first->elemname,elem);
 first->elemdata=malloc(lengt+1);
 strcpy(first->elemdata,value);
 first->link=NULL;
 *q=first;
}
else
{
first = *q;
while(first->link !=NULL)
{
first=first->link;
}
next = malloc(sizeof(*next));
next->leng=lengt;
next->elemname=malloc(strlen(elem)+1);
strcpy(next->elemname,elem);
next->elemdata=malloc(lengt+1);
strcpy(next->elemdata,value);
next->link=NULL;
first->link=next;

}

}