XML Parse with same tag name

[shrey3a]

Hi,
I've never worked with XML string and we have below XML string
<msg><trans><InsertRow>
<col name="PARTY_ID" isKey="1"><char>1INSER </char></col>
<col name="PART_CD" isKey="1"><char>s </char></col>
<col name="INDIV_VER_GMTS" isKey="1"><timestamp>2008-04-22T10:39:06.097000</timestamp></col>
<col name="BIRTH_DT"><date xsi:nil="1"/></col>
<col name="BIRTH_DT_MIS_CD"><char>s</char></col>
<col name="DEATH_DT"><date>2008-04-22</date></col>
</insertRow></trans></msg>

I defined my xml derivation as /msg/trans/insertRow/col/@name

But problem is it always bring the first column and split it in to many rows b'cos all the column has name as col attribute is ther a way the whole data can be bought up in one row.

regards,

[eostic]

...any chance this XML is coming from Event Publisher for Changed Data Capture? I've seen it before. An "ugly" XML design, although beauty is in the eyes of the beholder.... One of XML's great values is that the tags represent metadata...in this example the "tags" are just place holders for "column"....while the first attribute called NAME is the real meta data......

You need to get it out of XML first, as two columns, NAME and ...one of your datatypes (you'll have many possibilities, for sure. Once you get them all into their individual rows, you'll need to reverse pivot it back into one single row. I have to check my notes -- you may be able to mask the datatype element with /*/text() [do a search thru the forum and find the xml best practices document on kduke's great resource site]....but you'll still need a reverse pivot afterwards to get things to one row.

Ernie

[kduke]

Follow my link below my sig.