How to read XML file data?

[RameshChandraKeer]

Hi,

I have an XML file need to read data from this.The contents of XML file as below:

<ABCD>

<USER  Name="Jorge" LastName="Frenklin London "  Age=25 />

<USER  Name="Abdul" LastName="J Mclean" Age=50 />

</ABCD>

Now after reading above file I need data in below format:

Name     LastName               Age

Jorge     Frenklin London        25

Abdul     J Mclean                  50

Please,anyone help how can I get this?

Thanks,

[Tom]

If your file can be made to conform to standard XML format then you could use the SAS XML Mapper tool to generate and XML mapping that you could use with the XML engine for a libname statement.

If the lines are simple like your example and the values always appear on one line then you could possible read it with a data step like this.

filename  sample 'C:\downloads\sample.xml';

data user ;

  infile sample ;

  input @ ;

  if index(_infile_,'<USER');

  length name lastname $100 age 8 ;

  l1=index(_infile_,'Name=');

  l2=index(_infile_,'LastName=');

  l3=index(_infile_,'Age=');

  if l1 then input @l1 + 5 name :& $quote100.  @ ;

  if l2 then input @l2 + 9 lastname :& $quote100.  @ ;

  if l3 then input @l3 + 4 age @;

  drop l1 l2 l3 ;

run;

[RameshChandraKeer]

Hi Tom,

Thanks a lot.

It's working fine.

Now I am looking to make it generic.

For example suppose in future I want to add one more column Address in XML file, In such case I need to update the sas code to read XML file.

Is it possible to make it generic?

[Tom]

If you create a well formed XML file then you can use the XML mapper tool to generate a map that you can use with the XML libname engine.  I am not an XML expert but I was able to try and open the XML file with Windows Explorer and it did not like the construct AGE=25.  It wanted quotes around the 25.  Once I did this I was able to get the XML mapper tool to read the file.

Here is a simple map that can be used to read the file (once the quotes are added).

<?xml version="1.0" encoding="windows-1252"?>
<SXLEMAP version="1.2" >
    <TABLE name="USER">
        <TABLE-PATH syntax="XPath">/ABCD/USER</TABLE-PATH>

        <COLUMN name="Name">
            <PATH syntax="XPath">/ABCD/USER/@Name</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>5</LENGTH>
        </COLUMN>

        <COLUMN name="LastName">
            <PATH syntax="XPath">/ABCD/USER/@LastName</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>16</LENGTH>
        </COLUMN>

        <COLUMN name="Age">
            <PATH syntax="XPath">/ABCD/USER/@Age</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

    </TABLE>

</SXLEMAP>

Then you can use this program to read the table USER.

filename sample 'sample.xml';

filename sampmap 'sample.map';

libname sample xml92 xmlmap=sampmap access=readonly;

proc contents data=sample._all_; run;

proc print data=sample.user; run;

[AhmedAl_Attar]

Ramesh,

Check the SAS(R) 9.2 XML LIBNAME Engine: User's Guide, Second Edition , it has few sections and examples on how to import XML document.

Hope this helps,

Ahmed