Sat Oct 20, 2018 3:58 am
Login Register Lost Password? Contact Us


Load XML Inline into a dataset?

Comments and questions related to the Enterprise Control Language

Wed Nov 22, 2017 4:22 pm Change Time Zone

This might be a pretty basic question, I can't find any examples in the documentation though. I'm trying to load some XML data into a dataset like so:

Code: Select all
test := '<row><name>greg</name></row>';

r := {
   string name {xpath('name')};
};

DS := DATASET(test, r, XML('row'));
DS;


Of course it tries to open a logical file with the string assigned to test. I feel like there was a way to do this but can't figure it out.
BGehalo
 
Posts: 13
Joined: Thu Sep 28, 2017 8:06 pm

Tue Nov 28, 2017 6:29 pm Change Time Zone

BGehalo,

Unfortunately, inline DATASETs do not support the XML option. You can remove the XML option and then you will have a single variable length STRING field that you can use PARSE to extract your xml, but if what you want to do is develop code to operate on XML datasets, then you'll need to write your test XML data to a disk file that a standard XML DATASET can operate on.

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1388
Joined: Wed Oct 26, 2011 7:40 pm

Mon May 21, 2018 2:43 pm Change Time Zone

Hi,
If you just want to test something out, read up FROMXML in the ECL Ref manual.
here is a bit of an example hack I did to get my ideas straight:
Code: Select all
NameRec := RECORD
STRING Firstname{xpath('fname')};
STRING Lastname{xpath('surname')};
END;

namesRec := RECORD
UNSIGNED4 EmployeeID{xpath('NI')};
DATASET(NameRec) names{xpath('name')};
END;

PeopleRec := RECORD
    DATASET(NamesRec) People{xpath('/People')};
END;

x := '<Row>'
+'<People>'
+'<NI>1234567</NI>'
+'<names>'
+'<name><fname>Allan</fname><surname>Wrobel</surname></name>'
+'<name><fname>Anna</fname><surname>Smith</surname></name>'
+'<name><fname>Nina</fname><surname>Harrison</surname></name>'
+'</People>'
+'<People>'
+'<NI>98765</NI>'
+'<name><fname>Colin</fname><surname>Harison</surname></name>'
+'<name><fname>James</fname><surname>Wilson</surname></name>'
+'<name><fname>Nick</fname><surname>Pine</surname></name>'
+'</People>'
+'</Row>';
rec := FROMXML(PeopleRec,x);
OUTPUT(rec.People);
p2 := rec.people[2];

p2.EmployeeID;
p2.names[3].Firstname;

Yours
Allan
Allan
 
Posts: 318
Joined: Sat Oct 01, 2011 7:26 pm

Mon May 21, 2018 5:22 pm Change Time Zone

Allan,

I tried your code and got some errors so I took the liberty of making a couple of minor (but important) modifications to your code :)
Code: Select all
NameRec := RECORD
  STRING Firstname{xpath('fname')};
  STRING Lastname{xpath('surname')};
END;

namesRec := RECORD
  UNSIGNED4 EmployeeID{xpath('NI')};
  DATASET(NameRec) names{xpath('names/name')}; //note the xpath
END;

PeopleRec := RECORD
    DATASET(NamesRec) People{xpath('/People')};
END;

x := '<Row>'
+'<People>'
+'<NI>1234567</NI>'
+'<names>'                //you had this one, so I added the ones below
+'<name><fname>Allan</fname><surname>Wrobel</surname></name>'
+'<name><fname>Anna</fname><surname>Smith</surname></name>'
+'<name><fname>Nina</fname><surname>Harrison</surname></name>'
+'</names>'               //added
+'</People>'
+'<People>'
+'<NI>98765</NI>'
+'<names>'               //added
+'<name><fname>Colin</fname><surname>Harison</surname></name>'
+'<name><fname>James</fname><surname>Wilson</surname></name>'
+'<name><fname>Nick</fname><surname>Pine</surname></name>'
+'</names>'               //added
+'</People>'
+'</Row>';
rec := FROMXML(PeopleRec,x);

OUTPUT(rec);               //added
   /* <NI>1234567</NI>
   <names><name><fname>Allan</fname><surname>Wrobel</surname>
   </name><name><fname>Anna</fname><surname>Smith</surname></name>
   <name><fname>Nina</fname><surname>Harrison</surname></name></names>
   <NI>98765</NI>
   <names><name><fname>Colin</fname><surname>Harison</surname>
   </name><name><fname>James</fname><surname>Wilson</surname>
   </name><name><fname>Nick</fname><surname>Pine</surname></name></names> */

OUTPUT(rec.People);

p2 := rec.people[2];
p2.EmployeeID;         //98765
p2.names[3].Firstname; //Nick

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1388
Joined: Wed Oct 26, 2011 7:40 pm

Tue May 22, 2018 10:47 am Change Time Zone

Hum,

I don't get any errors from my original code, it all runs ok and returns expected results..
I'm using client 6.2.0 and server is 6.2.26

Yours
Allan
Allan
 
Posts: 318
Joined: Sat Oct 01, 2011 7:26 pm

Wed May 23, 2018 1:33 pm Change Time Zone

Allan,

OK, your original code DOES work if I comment out this line:
Code: Select all
+'<names>'


:)

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1388
Joined: Wed Oct 26, 2011 7:40 pm

Tue Aug 21, 2018 7:05 pm Change Time Zone

The suggestion from R Taylor only solves it for a single xml record (I was not able to make it work for a batch). I kept getting the following error -
Error: System error: 2: Error - syntax error "Trailing xml after close of root tag"

I figured out a way to translate a batch xml file to a dataset simply via PARSE -
Code: Select all
in_xml:=dataset([{
     '<row>'
      +'   <acctno>12</acctno>'
      +'   <addr>123 PAPAI ST</addr>'
      +'   <p_city_name>HONOLULU</p_city_name>'
      +'</row>'
      +'<row>'
      +'   <acctno>26</acctno>'
      +'   <addr>45 5TH ST</addr>'
      +'   <p_city_name>Honewyll</p_city_name>'
      +'</row>'
}],{string xml});

out_rec := record
  string20 acctno;
   string100 addr;
   string25 p_city_name;
end;

out_rec tran :=
   TRANSFORM
       SELF.acctno       := XMLTEXT('acctno');
       SELF.addr         := XMLTEXT('addr');
       SELF.p_city_name  := XMLTEXT('p_city_name');
   END;

PARSE(in_xml,xml,tran,XML('row'));
abittandan
 
Posts: 2
Joined: Mon Jan 29, 2018 3:03 pm

Wed Aug 22, 2018 2:07 pm Change Time Zone

After playing around with it, I finally got around the "trailing xml" error mentioned above and was able to do this via FROMXML with a batch as follows -

Code: Select all
addrRec := RECORD
  string20 acctno{xpath('acctno')};
   string100 addr{xpath('addr')};
   string25 p_city_name{xpath('p_city_name')};
END;

finalRec := RECORD
    DATASET(addrRec) ds{xpath('/row')};
END;

x := '<Batch_in>'
            +'<row>'
            +'   <acctno>12</acctno>'
            +'   <addr>123 PAPAI ST</addr>'
            +'   <p_city_name>HONOLULU</p_city_name>'
            +'</row>'
            +'<row>'
            +'   <acctno>26</acctno>'
            +'   <addr>45 5TH ST</addr>'
            +'   <p_city_name>Honewyll</p_city_name>'
            +'</row>'
    +'</Batch_in>';
      
FROMXML(finalRec,x).ds;


the only catch is that you need to extract the nested inner ds which is why it is more efficient to use PARSE for batches.
abittandan
 
Posts: 2
Joined: Mon Jan 29, 2018 3:03 pm


Return to ECL

Who is online

Users browsing this forum: No registered users and 0 guests

cron