Problem with getChild while parsing XML with Google Script

Question

Please could anyone help me with this, I'm stuck, I'm trying parse this XML data and insert it into a Google Sheet, I can fetch the XML correctly but after parsing, I'm getting a null with "getChild"..

This is a extract of the XML:

<?xml version="1.0" encoding="UTF-8" ?> 
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <SOAP-ENV:Body>
        <ns1:etaWebServicesResponse xmlns:ns1="http://ws.ABC.com/ABCws.wsdl" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
            <return xsi:type="xsd:string">
                <PERSONNEL>
                    <INSTRUCTOR>
                        <PERSONNEL_ID>0001</PERSONNEL_ID> 
                        <FIRST_NAME>Mike</FIRST_NAME> 
                    </INSTRUCTOR>
                    <INSTRUCTOR>
                        <PERSONNEL_ID>0002</PERSONNEL_ID> 
                        <FIRST_NAME>Joanna</FIRST_NAME>
                    </INSTRUCTOR>
            </return>
        </ns1:etaWebServicesResponse>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

And I'm trying with this Google App Script

function myFunction() {

  var xmlInstructor = "<?xml version='1.0' encoding='UTF-8'?><SOAP-ENV:Envelope xmlns:" + 
  'SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><SOAP-ENV:Body><etaws><operation opstype="instructor">export</operation><parameters><customercode>AAAAAAAA</customercode><accesscode>BBBBBBBBB</accesscode><username>xybvsfsddfas</username></parameters></etaws></SOAP-ENV:Body></SOAP-ENV:Envelope>';

  var url = "https://ABCsystems.com/tseta/servlet/ABC?xmldata=" + xmlInstructor;
  var urlx = encodeURI(url);
  var XMLFetch = UrlFetchApp.fetch(urlx).getContentText();

  var document = XmlService.parse(XMLFetch);

  var documentPretty = XmlService.getPrettyFormat().format(document);
  Logger.log("PrettyFormat:");
  Logger.log(documentPretty);

  Logger.log("Descendants:");
  Logger.log(document.getDescendants());

  var root = document.getRootElement();  
  Logger.log("getRootElement:" + root);

  var instructors = root.getChild("PERSONNEL");
  Logger.log("Personnel:" + instructors);
}

The log of document has the XML correctly as shown at the top.

The log of Descendants has the following info (first part):

[19-05-14 15:12:09:409 PDT] Descendants:
[19-05-14 15:12:09:427 PDT] [[Element: <SOAP-ENV:Envelope [Namespace: http://schemas.xmlsoap.org/soap/envelope/]/>], [Element: <SOAP-ENV:Body [Namespace: http://schemas.xmlsoap.org/soap/envelope/]/>], [Element: <ns1:etaWebServicesResponse [Namespace: http://ws.ABCsystems.com/ABC.wsdl]/>], [Element: <return/>], 
, [Element: <PERSONNEL/>], 
 , [Element: <INSTRUCTOR/>], 
  , [Element: <PERSONNEL_ID/>], 0001, 
  , [Element: <FIRST_NAME/>], Mike,

The log of getRootElement:

[19-05-14 15:12:09:428 PDT] getRootElement:[Element: <SOAP-ENV:Envelope [Namespace: http://schemas.xmlsoap.org/soap/envelope/]/>]

and finally instructors = null, I'm not getting to drill down. Any suggestions?

You want to retrieve values from the XML data at the top of your question. If my understanding is correct, what is var url = "https://ABCsystems.com/tseta/servlet/ABC?xmldata=" + xmlInstructor; var urlx = encodeURI(url); var XMLFetch = UrlFetchApp.fetch(urlx).getContentText();? If I misunderstood your question, I apologize. — Tanaike
– Tanaike, Commented May 14, 2019 at 22:37
Sorry maybe it's not too clear.. With that code I fetch the XML, that part is ok. The problem is after parsing, I can't get the data from the XML structure.. — Jean AL
– Jean AL, Commented May 14, 2019 at 23:06
Thank you for replying. Where can I see the retrieved XML data? — Tanaike
– Tanaike, Commented May 14, 2019 at 23:10
Thanks for your fast reply, it's on the first part of the post (variable XMLFetch) — Jean AL
– Jean AL, Commented May 14, 2019 at 23:13
Thank you for replying. I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your question and that was not the result you want, I apologize. — Tanaike
– Tanaike, Commented May 14, 2019 at 23:37

Tanaike · Accepted Answer · 2019-05-14 23:37:51Z

1

You want to retrieve the values of PERSONNEL from the XML data at the top of your question.

If my understanding is correct, how about this sample script?

Modified script:

var data = '<?xml version="1.0" encoding="UTF-8" ?> <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><SOAP-ENV:Body><ns1:etaWebServicesResponse xmlns:ns1="http://ws.ABC.com/ABCws.wsdl" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><return xsi:type="xsd:string"><PERSONNEL><INSTRUCTOR><PERSONNEL_ID>0001</PERSONNEL_ID> <FIRST_NAME>Mike</FIRST_NAME> </INSTRUCTOR><INSTRUCTOR><PERSONNEL_ID>0002</PERSONNEL_ID> <FIRST_NAME>Joanna</FIRST_NAME></INSTRUCTOR></PERSONNEL></return></ns1:etaWebServicesResponse></SOAP-ENV:Body></SOAP-ENV:Envelope>';
var xml = XmlService.parse(data);
var rootChildren = xml.getRootElement().getChildren();
for (var i = 0; i < rootChildren.length; i++) {
  if (rootChildren[i].getName() == "Body" ) {
    var c1 = rootChildren[i].getChildren();
    for (var j = 0; j < c1.length; j++) {
      var c2 = c1[j].getChild("return").getChild("PERSONNEL").getChildren();
      for (var k = 0; k < c2.length; k++) {
        var PERSONNEL_ID = c2[k].getChild("PERSONNEL_ID").getValue();
        var FIRST_NAME = c2[k].getChild("FIRST_NAME").getValue();
        Logger.log("PERSONNEL_ID: %s, FIRST_NAME: %s", PERSONNEL_ID, FIRST_NAME)
      }
    }
  }
}

Note:

Your XML data at the top of your question is not completed. So in order to use XmlService.parse(), I added </PERSONNEL> before </return>.

Reference:

Class XmlService

answered May 14, 2019 at 23:37

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jean AL Over a year ago

Thanks so much! I really appreciated it!

Tanaike Over a year ago

@Jean AL I'm glad your issue was resolved. Thank you, too.

Collectives™ on Stack Overflow

Problem with getChild while parsing XML with Google Script

1 Answer 1

Modified script:

Note:

Reference:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Note:

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related