XML O/p formatting issue

shaonli · Post by **shaonli** » Thu Jul 17, 2008 4:48 am

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

eostic · Post by **eostic** » Thu Jul 17, 2008 5:22 am

...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

shaonli · Post by **shaonli** » Thu Jul 17, 2008 5:37 am

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

Thanks In Advance
Shaonli

ray.wurlod · Post by **ray.wurlod** » Thu Jul 17, 2008 5:45 am

As has so often been explained, we at DSXchange simply don't do "urgent". If it is urgent, as you say, sign up for premium service with your support provider, and learn how expensive "urgent" is. DSXchange is a community of volunteers who post as and when they can, and then only if they have something concrete to contribute.

shaonli · Post by **shaonli** » Thu Jul 17, 2008 5:53 am

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

Thanks in advance
Shaonli

chulett · Post by **chulett** » Thu Jul 17, 2008 6:27 am

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

eostic · Post by **eostic** » Thu Jul 17, 2008 7:31 am

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

chulett · Post by **chulett** » Thu Jul 17, 2008 8:29 am

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

eostic · Post by **eostic** » Thu Jul 17, 2008 9:23 am

...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

chulett · Post by **chulett** » Thu Jul 17, 2008 9:27 am

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

ray.wurlod · Post by **ray.wurlod** » Thu Jul 17, 2008 3:17 pm

He drove the fastest milk cart in the West.

eostic · Post by **eostic** » Thu Jul 17, 2008 3:58 pm

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.

chulett · Post by **chulett** » Thu Jul 17, 2008 4:33 pm

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.

shaonli · Post by **shaonli** » Mon Jul 21, 2008 2:42 am

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

shaonli · Post by **shaonli** » Mon Jul 21, 2008 3:48 am

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