How do I modify an existing XML file

jotabe · Posted 06-06-2018 01:34 PM

Hi all

I have a XML file as follow:

<?xml version="1.0" encoding="Windows-1252"?>
<new_examples>
<element_first>

</element_first>

<examples_list>

<example_element>

</example_element>

<example_element>

</example_element>

</examples_list>

</new_examples>

Then I have a table Table1 like:

Name Amount

a 3

b 12

And I want to add the amount to the XML so I end up with:

<?xml version="1.0" encoding="Windows-1252"?>
<new_examples>
<element_first>

</element_first>

<examples_list>

<example_element>

</example_element>

<example_element>

</example_element>

</examples_list>

</new_examples>

I read the XML as:

filename xx temp;
libname xx xmlv2 '\\path\test_xml' automap=replace xmlmap=xx;

It lets me access the information of the XML in SAS, having the table example_element with the two example_element in the list.

But then I don't know how I can modify the XML as if I try to overwrite the table xx.example_element but it does not work.

I am trying first to do a dummy example adding a fixed amount = 10 in all the example_element like:

data xx.example_element;
set xx.example_element;
amount = 10;
run;

This is the log:

WARNING: Output column amount does not occur in XMLMap definition of table example_element.
ERROR: XMLMap for output table example_element requested but that table did not exist in the XMLMap output section.
ERROR: Invalid sequence of commands for file XX.example_element.DATA.

I am using SAS Enterprise Guide 7.1

ChrisNZ · Posted 06-06-2018 11:30 PM

In your example, you let SAS define the XML map from the existing file.

Since the existing file does not have an AMOUNT value, the map doesn't either, and therefore you can't insert values into a column that doesn't exist.

The best option is to modify the map. If you run

data tt; infile XX; input; put _infile_; run;

you see that SAS created this map:

<?xml version="1.0" encoding="UTF-8"?>
<!-- ############################################################ -->
<!-- 2018-06-07T15:13:13 -->
<!-- SAS XML Libname Engine Map -->
<!-- Generated by XML Mapper, 904000.0.0.20130522190000_v940 -->
<!-- ############################################################ -->
<!-- ###  Validation report                                   ### -->
<!-- ############################################################ -->

<!-- XMLMap validation completed successfully. -->
<!-- ############################################################ -->
<SXLEMAP name="AUTO_GEN" version="2.1">
    <NAMESPACES count="0"/>
    <!-- ############################################################ -->
    <TABLE description="new_examples" name="new_examples">
        <TABLE-PATH syntax="XPath">/new_examples</TABLE-PATH>
        <COLUMN class="ORDINAL" name="new_examples_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
    </TABLE>
    <!-- ############################################################ -->
    <TABLE description="element_first" name="element_first">
        <TABLE-PATH syntax="XPath">/new_examples/element_first</TABLE-PATH>
        <COLUMN class="ORDINAL" name="new_examples_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN class="ORDINAL" name="element_first_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples/element_first</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN name="type">
            <PATH syntax="XPath">/new_examples/element_first/type</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN name="state">
            <PATH syntax="XPath">/new_examples/element_first/state</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
    </TABLE>
    <!-- ############################################################ -->
    <TABLE description="examples_list" name="examples_list">
        <TABLE-PATH syntax="XPath">/new_examples/examples_list</TABLE-PATH>
        <COLUMN class="ORDINAL" name="new_examples_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN class="ORDINAL" name="examples_list_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples/examples_list</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
    </TABLE>
    <!-- ############################################################ -->
    <TABLE description="example_element" name="example_element">
        <TABLE-PATH syntax="XPath">/new_examples/examples_list/example_element</TABLE-PATH>
        <COLUMN class="ORDINAL" name="examples_list_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples/examples_list</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN class="ORDINAL" name="example_element_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples/examples_list/example_element</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN name="name">
            <PATH syntax="XPath">/new_examples/examples_list/example_element/name</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>1</LENGTH>
        </COLUMN>
        <COLUMN name="status">
            <PATH syntax="XPath">/new_examples/examples_list/example_element/status</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
    </TABLE>
</SXLEMAP>

Create a new map and add a new section for the new column

        <COLUMN name="amount">
            <PATH syntax="XPath">/new_examples/examples_list/example_element/amount</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

and use that map to write out the new table.

High-Performance SAS Coding - Third Edition

jotabe · Posted 06-07-2018 03:53 AM

Thanks for your reply ChrisNZ.

However, this is the first time I work with XML in SAS and I am quite lost.

I do not know how to "Create a new map and add a new section for the new column" neither "use that map to write out the new table", Also not sure how I can once I have that new table, how I end up with a XML as the one in my example?

I would appreciate a code step by step if that's not too much on how to add the amount = 10 for both, it looks like it must be a couple of easy data steps but I don't know what I should do.

I am trying to see how to do this in the documentation but I cannot see anything similar to what I need, just how to read in XMLs and how to export easy XMLs (with less complexity than the one I have). So if someone could point me out what I should look for in the documentation. This is the first time I struggle with SAS documentation until the point of opening a question in this forum and netiher I find a similar question in the forum...

ChrisNZ · Posted 06-07-2018 08:20 PM

Here is a working example. The map of your output "table", i.e. XML file, must be similar to the one below.


data _null_;
  file "%sysfunc(pathname(WORK))\output.map" lrecl=32000;
  put'
<?xml version="1.0" encoding="UTF-8"?>

<SXLEMAP version="1.9">   

  <OUTPUT>     
    <HEADING>       
      <ATTRIBUTE name="description" value="xml output"/>     
    </HEADING>     
    <TABLEREF name="OUTTABLE"/>   
  </OUTPUT> 
 
  <TABLE name="OUTTABLE">     
    <TABLE-PATH syntax="XPath">/xml/lev1/lev2/OUTTABLE</TABLE-PATH>     
    <COLUMN name="DESCRIPTION">       
      <PATH syntax="XPath">/xml/lev1/lev2/OUTTABLE/DESCRIPTION</PATH>       
      <TYPE>character</TYPE>       
      <DATATYPE>string</DATATYPE>       
      <LENGTH>50</LENGTH>     
    </COLUMN>     
    <COLUMN name="COUNT">       
      <PATH syntax="XPath">/xml/lev1/lev2/OUTTABLE/COUNT</PATH>       
      <TYPE>numeric</TYPE>            
      <DATATYPE>integer</DATATYPE>       
    </COLUMN>     
    <COLUMN name="PERCENT">       
      <PATH syntax="XPath">/xml/lev1/lev2/OUTTABLE/PERCENT</PATH>       
      <TYPE>numeric</TYPE>       
      <DATATYPE>double</DATATYPE>       
    </COLUMN>   
  </TABLE>

</SXLEMAP> ';
;run ;

    
filename OUT    "%sysfunc(pathname(WORK))\output.xml";
filename OUTMAP "%sysfunc(pathname(WORK))\output.map";
libname  OUT xml92 xmlmap=OUTMAP;

data OUT.OUTTABLE;              
  length DESCRIPTION $100;
  retain  DESCRIPTION 'abc' COUNT 22 PERCENT 2.2;
run;


%* Dump contents of output file;
data _null_; infile OUT; input; put _infile_ ; run;

<?xml version="1.0" encoding="windows-1252" ?>
<!--
      SAS XML Libname Engine (SAS92XML)
      SAS XMLMap Generated Output
      Version 9.04.01M2P07232014
      Created 2018-06-08T12:19:09
  -->

<xml description="xml output">
<lev1>
<lev2>
<OUTTABLE>
<DESCRIPTION>abc</DESCRIPTION>
<COUNT>22</COUNT>
<PERCENT>2.2</PERCENT>
</OUTTABLE>
</lev2>
</lev1>
</xml>

High-Performance SAS Coding - Third Edition

maggiem_sas · Posted 06-11-2018 01:45 PM

I think you could use the XML Mapper application to modify the map.

http://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=engxml&docsetTarget=n0m...

ChrisNZ · Posted 06-11-2018 05:46 PM

@maggiem_sas

Sadly most SAS developers these days have to endure SAS EG with nothing else available, because of SAS Institute licencing choices.

I wish SAS employees ate the same dog food and only used EG as well. This would without a doubt ensure swift changes.

High-Performance SAS Coding - Third Edition

maggiem_sas · Posted 06-11-2018 06:37 PM

The SAS XML Mapper does not require a license.

ChrisNZ · Posted 06-11-2018 06:44 PM

Regardless, XML mapper never gets deployed with EG. It is probably not even supplied, and can certainly not be installed by downloading it in most corporations. I recall XML mapper used to get deployed with desktop SAS, together with graph'n'go, licence updater, etc.

I am only mentioning the licence because that's how SAS Institute killed SAS desktop.

High-Performance SAS Coding - Third Edition

How do I modify an existing XML file

Re: How do I modify an existing XML file

Re: How do I modify an existing XML file

Re: How do I modify an existing XML file

Re: How do I modify an existing XML file

Re: How do I modify an existing XML file

Re: How do I modify an existing XML file

Re: How do I modify an existing XML file

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away