SAS xml mapper empty tables

[hkelop1]

Hello Community,

I would like to import a xml file. Therefore I use the SAS XML- Mapper to create a map.

My xml files contains a few rows without any values (red line):

<teacher>

     <lastname>John</lastname>

</teacher>

<n12:name/>

<landscape>

     <forest>tree</forest>

</landscape>

My problem is that the XML-Mapper does not create a dataset out of those rows without any values only for the other rows. Is there any possiblity to get this done either directly in the XML-Mapper or as an automated workaround.

Thanks in advance!

[BillM_SAS]

Please post the data set output you desire based on the example XML file fragment that you posted.

[hkelop1]

I would like to have a dataset teacher with a column John and an empty dataset n12:name and a dataset landscape with the column tree. At the moment it works for the dataset John and for the dataset landscape, but it seems to ignore the n12:name. I would like to get also an empty dataset with the name n12:name.

[BillM_SAS]

I created a XML Mapper file with the SAS XML Mapper application that does almost everything you requested. I added code to you example XML fragment to make it valid XML. Here is the modified XML file:

<?xml version="1.0" encoding="iso-8859-1" ?>

<root xmlns:n12="http://www.w3.org/2001/XMLSchema">

  <teacher>

    <lastname>John</lastname>

  </teacher>

  <n12:name/>

  <landscape>

    <forest>tree</forest>

  </landscape>

</root>

I then loaded that XML into the SAS XML Mapper application.

SAS XML Mapper steps:

1. Click the "SXLEMAP" element in the tree view of the XML Map Primary Pane (top right window). In the "Name" field above that pane, rename "SXLEMAP" to "test_2".
2. Drag and drop "teacher[1][1]" from the XML Primary Pane (top left window) onto the "test_2" element in the tree view of the XML Map Primary Pane (top right window).
3. Do the same for "name[1][1]" and "landscape[1][1]".
4. To make the "name" data set name match what you want, click on the "name" element in the XML Map Primary Pane (top right window) and change the data set name in the "Name" field above this pane. Note that the SAS XML Mapper will complain that "n12:name" is not a valid SAS name. I named this data set "n12_name" to make it a valid SAS name. I'm not sure if "n12:name" as the data set name is a hard requirement for you.
5. In the XML Map Primary Pane (top right window) expand the "teacher" element by clicking the plus sign, "+", to the left of "teacher[1][1]". This will cause "lastname[1][1]" to appear below "teacher[1][1]". Drag and drop "lastname[1][1]" onto "teacher" in the XML Map Primary Pane (top right window).
6. Do the same for "landscape[1][1]".
7. For the "name[1][1]" in the XML Primary Pane (top left window), just drag and drop that onto the "n12:name" element in the XML Map Primary Pane (top right window).
8. Use File->Save XML Map As... to save the XML Mapper file. I choose the name test_2.map.

This resulted in this XML Mapper file:

<?xml version="1.0" encoding="UTF-8"?>

<!-- ############################################################ -->

<!-- 2015-08-11T16:13:57 -->

<!-- SAS XML Libname Engine Map -->

<!-- Generated by XML Mapper, 903200.3.0.20120523190000_v930m2 -->

<!-- ############################################################ -->

<SXLEMAP name="test_2" version="2.1">

    <NAMESPACES count="1">

        <NS id="1" prefix="n12">http://www.w3.org/2001/XMLSchema</NS>

    </NAMESPACES>

    <!-- ############################################################ -->

    <TABLE name="teacher">

        <TABLE-PATH syntax="XPath">/root/teacher</TABLE-PATH>

        <COLUMN name="lastname">

            <PATH syntax="XPath">/root/teacher/lastname</PATH>

            <TYPE>character</TYPE>

            <DATATYPE>string</DATATYPE>

            <LENGTH>4</LENGTH>

        </COLUMN>

    </TABLE>

    <!-- ############################################################ -->

    <TABLE name="n12_name">

        <TABLE-PATH syntax="XPathENR">/root/{1}name</TABLE-PATH>

        <COLUMN name="name">

            <PATH syntax="XPathENR">/root/{1}name</PATH>

            <TYPE>character</TYPE>

            <DATATYPE>string</DATATYPE>

            <LENGTH>32</LENGTH>

        </COLUMN>

    </TABLE>

    <!-- ############################################################ -->

    <TABLE name="landscape">

        <TABLE-PATH syntax="XPath">/root/landscape</TABLE-PATH>

        <COLUMN name="forest">

            <PATH syntax="XPath">/root/landscape/forest</PATH>

            <TYPE>character</TYPE>

            <DATATYPE>string</DATATYPE>

            <LENGTH>4</LENGTH>

        </COLUMN>

    </TABLE>

</SXLEMAP>

In the SAS session, I use the XMLV2 LIBNAME engine to create a libref to the XML file using the XML Mapper file just created. Running the DATASETS procedure shows the data sets created and the PRINT procedure shows what you desired. The only difference from what you specified is the name of the "n12_name" data set (as noted above).

libname x xmlv2 "My Documents/292721/test_1.xml"

                xmlmap="My Documents/292721/test_2.map";

proc datasets lib=x; run; quit;

proc print data=x.n12_name; run;

proc print data=x.teacher; run;

proc print data=x.landscape; run;

[hkelop1]

Hi BILLM@SAS,

thanks for your description. It sounds like what I am looking for! I try to find time next week, if it works out for me for my real XML-FIle.

Best regards,

Eric