DSXchange

My i/p has 3 columns.I/p is---


OWNING GROUP SECURITY-LVL GROUP-NUMBER

TIGERPRINT 4 0
TIGERPRINT1 5 1
TIGERPRINT2 6 2


Expected o/p is

<RECORDS>
<RECORD>
<PROP NAME="OWNING-GROUP">
<PVAL>TIGERPRINT</PVAL>
</PROP>
<PROP NAME="SECURITY-LVL">
<PVAL>4</PVAL>
</PROP>
<PROP NAME="GROUP-NUMBER">
<PVAL>0</PVAL>
</PROP>
</RECORD>
<RECORD>
<PROP NAME="OWNING-GROUP">
<PVAL>TIGERPRINT1</PVAL>
</PROP>
<PROP NAME="SECURITY-LVL">
<PVAL>5</PVAL>
</PROP>
<PROP NAME="GROUP-NUMBER">
<PVAL>1</PVAL>
</PROP>
</RECORD>
<RECORD>
<PROP NAME="OWNING-GROUP">
<PVAL>TIGERPRINT2</PVAL>
</PROP>
<PROP NAME="SECURITY-LVL">
<PVAL>6</PVAL>
</PROP>
<PROP NAME="GROUP-NUMBER">
<PVAL>2</PVAL>
</PROP>
</RECORD>
</RECORDS>



Xpath expressions are

/RECORDS/RECORD/PROP[@NAME="OWNING-GROUP"]/PVAL/text()
/RECORDS/RECORD/PROP[@NAME="SECURITY-LVL"]/PVAL/text()
/RECORDS/RECORD/PROP[@NAME="GROUP-NUMBER"]/PVAL/text()


But o/p which I am getting is

<RECORDS>
<RECORD>
<PROP NAME="OWNING-GROUP"><PVAL>TIGERPRINT4</PVAL>0</PROP>
<PROP NAME="OWNING-GROUP"><PVAL>TIGERPRINT15</PVAL>1</PROP>
<PROP NAME="OWNING-GROUP"><PVAL>TIGERPRINT26</PVAL>2</PROP>
</RECORD>
</RECORDS>

Please check what is wrong with Xpath expressions.

Thanks
Shaonli

...another xml document that sadly does not use the column names as the element names, so that it reflects the real meta data...... (would have been nicer if you had an element by the name of <OwningGroup>.

Simplest thing for you to do would be to first pivot the data, meaning, get each column into its own row. Then you will more easily be able to generate the repeating PROP element instances without having to resort to any custom XPath work.

Ernie

Can you please explain this more explicitly.
This is an urgent requirement.

Thanks In Advance
Shaonli

Sorry Ray.....
If it is possible then please provide the solution for it.

Thanks in advance
Shaonli

And someone will - if they can and when they can. If you truly need 'urgent' help, contact your support provider.

Craig is right. ...and now I have a few minutes.... what you need to do is turn your 3 rows in the example above into 9 rows.... (which is what you really have in your desired XML). There are 9 "prop" elements. This type of issue usually occurs with columns that are of similar type, so for each row you need to define a pivot that will generate 3 separate rows (one for each of your pval "sets"). Get that accomplished first (search in the forum on pivot --- I'm sure there are 100's of threads on it).

Once you have nine rows, a more simplistic set of XPath will produce what you are looking for, where a column containing the "NAME" is generated with xpath that ends in @NAME, and the others are generated with simpler XPATH that ends in text(), without the explicit values in [] being needed any longer.

Again -- resolve the pivot technique first.

Ernie

Look, you lucked out - Uncle Ernie back from Holiday Camp so soon.

...I can always guage people's age by which historical media "ernie" they relate me to....... (with all due respect to "The Who" there are some other classic ones).

-e

I'm gonna have to check out the "historical Ernie" list, it seems.

let's see... I get "Sesame Street".....Verne and Ernest (some off-beat country bumkin --- comedy or a series, not sure which)......My Three Sons (60's TV show)....Ernie Kovacs show (my parents' friends)......the move "It's a Wonderful Life"......and there are probably a few more.

eostic wrote:Verne and Ernest (some off-beat country bumkin --- comedy or a series, not sure which)

The late Jim Varney played a character named "Ernest" and his registered trademark catch phrase was "KnoWhutImean, Vern?". Yup, spelt just like that.

According to your suggestion I have pivoted the recors.After pivotting I am getting 9 records.
Then I am using the Xpath as
/RECORDS/RECORD/PROP/PVAL/text()

The o/p is

<RECORDS>
<RECORD>
<PROP NAME="OWNING-GROUP">
<PVAL>TIGERPRINT
</PVAL>
</PROP>
<PROP NAME="SECURITY_LVL">
<PVAL>4
</PVAL>
</PROP>
<PROP NAME="GROUP_NUMBER">
<PVAL>0
</PVAL>
</PROP>
<PROP NAME="OWNING-GROUP">
<PVAL>TIGERPRINT1
</PVAL>
</PROP>
<PROP NAME="SECURITY_LVL">
<PVAL>5
</PVAL>
</PROP>
<PROP NAME="GROUP_NUMBER">
<PVAL>1
</PVAL>
</PROP>
<PROP NAME="OWNING-GROUP">
<PVAL>TIGERPRINT2
</PVAL>
</PROP>
<PROP NAME="SECURITY_LVL">
<PVAL>6
</PVAL>
</PROP>
<PROP NAME="GROUP_NUMBER">
<PVAL>2
</PVAL>
</PROP>
</RECORD>
</RECORDS>
But in the desried o/p is shown below.

<RECORDS>
<RECORD>
<PROP NAME="OWNING-GROUP">
<PVAL>TIGERPRINT</PVAL>
</PROP>
<PROP NAME="SECURITY-LVL">
<PVAL>4</PVAL>
</PROP>
<PROP NAME="GROUP-NUMBER">
<PVAL>0</PVAL>
</PROP>
</RECORD>
<RECORD>
<PROP NAME="OWNING-GROUP">
<PVAL>TIGERPRINT1</PVAL>
</PROP>
<PROP NAME="SECURITY-LVL">
<PVAL>5</PVAL>
</PROP>
<PROP NAME="GROUP-NUMBER">
<PVAL>1</PVAL>
</PROP>
</RECORD>
<RECORD>
<PROP NAME="OWNING-GROUP">
<PVAL>TIGERPRINT2</PVAL>
</PROP>
<PROP NAME="SECURITY-LVL">
<PVAL>6</PVAL>
</PROP>
<PROP NAME="GROUP-NUMBER">
<PVAL>2</PVAL>
</PROP>
</RECORD>
</RECORDS>

The i/p is

OWNING GROUP SECURITY-LVL GROUP-NUMBER

TIGERPRINT 4 0
TIGERPRINT1 5 1
TIGERPRINT2 6 2


Please sugggest.

Thanks
Shaonli

After pivotting the i/p I got

Input_Value,ID(which I added),Attribute
"TIGERPRINT","2","OWNING-GROUP"
"4","2","OWNING-GROUP"
"0","2","OWNING-GROUP"
"TIGERPRINT1","3","SECURITY-LVL"
"5","3","SECURITY-LVL"
"1","3","SECURITY-LVL"
"TIGERPRINT2","4","GROUP-NUMBER"
"6","4","GROUP-NUMBER"
"2","4","GROUP-NUMBER"



Now I have used the XPath


/RECORDS/RECORD/PROP/@NAME
/RECORDS/RECORD/PROP/PVAL/text()

and I got the records mentioned above.
But thing is that all 9 rows are coming under /RECORDS/RECORD/PROP structure

But each i/p record(3 i/p) comes under /RECORDS/RECORD structure.

Please suggest.

Thanks in advance
Shaonli