XML output

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

XML output

I need to generate xml/html output sample data set

data rbi_ofs;

input bank_code reporting_date nil $ reporting_statement $

aggregate_inward net_inflow outstanding_balance_inr    third_party_inward

third_party_outward  ;

informat reporting_date anydtdte32. ;

format reporting_date ddmmyy10.;

datalines;

12 25/06/2011 nil daily 1.00 1.74 20.42 0.00 0.00

;

run;

libname xmlout xml "path/sample.xml" xmlencoding ="UTF-8";

data xmlout.test;

set work.rbi_ofs;

run;

iam getting outupt in this format

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

- <table>

- <test>

<bank-code>13</bank-code>

<reporting-date>25/06/2011</reporting-date>

<nil>no</nil>

<reporting-statement>Daily</reporting-statement>

<aggregate-inward>1.00</aggregate-inward>

<aggregate-outward>0.26</aggregate-outward>

<net-inflow>1.74</net-inflow>

<outstanding-balance-inr>20.42</outstanding-balance-inr>

<outstanding-balance-foreign>1.75</outstanding-balance-foreign>

<third-party-inward>0.00</third-party-inward>

<third-party-outward>0.00</third-party-outward>

</test>

</table>

I want to remove '-' before table and test

and ;change the table with rbi-ofs


attached word

i am using ods xml but the output is not matching

is there any other way to generate xml file

Attachment

Accepted Solutions
Solution
‎05-31-2012 11:23 PM
SAS Super FREQ
Posts: 8,866

Re: XML output


Hi:

  First you need to distinguish between what you want and what you are getting. I'm not clear on which is which. Possibly, I think that what you WANT is shown in the ZIP file and what you are actually getting is what you posted underneath your program???.

  Next, are you looking at your SAMPLE.XML file in Notepad or with a browser. If you look at your XML file in a browser, you will see a "collapse/expand" character (a plus ( + ) or minus ( - ) in a few places in the file. If you open the file with Notepad, or a regular ASCII text editor, you will not see the + or -.

  Then, are you sure that your program is producing the output that you pasted into your posting? I do NOT see how the XML Libname Engine would be giving a hypen or dash in the XML element derived from the SAS columns name. Especially since a hypen or minus or dash in a variable name (such as bank-code) is invalid syntax and your program shows a variable name of bank_code. You should be seeing <bank_code>...</bank_code>  (with an underscore, like in the variable name).

  You say you need to produce XML/HTML -- but, which do you need to produce? XHTML is a hybrid HTML markup language that conforms  to XML validity rules. But what you showed in your ZIP file was some type of XML file. I did not see ANY HTML tags in that file. If you need to produce an XML file from a SAS dataset, then you were on the right track using the SAS XML Libname Engine.

  ODS TAGSETS.EXCELXP does produce XML -- but not the type of XML that you showed in your ZIP file. ODS TAGSETS.EXCELXP produces Microsoft Spreadsheet Markup Language 2003 XML which does not look at all like what was in your zip file. Do you need to create Excel specific XML?

  For example, look at the attached screen shot. The root tag <TABLE> </TABLE> is constructed by the XML LIBNAME engine. It is the standard root tag used by the engine. The XML engine also inserts the XML tag at the top of the file (<?xml version="1.0" encoding="utf-8" ?>) This is the standard XML tag inserted by the LIBNAME engine.

  My sample dataset only had 1 observation. Because I called my dataset XMLOUT.FIIS, the "FIIS" data set name was used for the "observation" tag. If i had more than 1 observation, every obs would be delimited by an FIIS tag. But since I only have 1 obs, I only have 1 FIIS tag. If you wanted to change the <TABLE></TABLE> to something else, then you would have to use other techniques to change the root tag. The documentation for the XML Libname Engine talks about using either an XMLMAP file to do your export to a different structure, or you could construct a custom tagset template to use with the LIBNAME engine to change the TABLE tag to be something else. Or, you could post-process the XML file in order to get the - into the tags and to rename the TABLE tag to be something else.

cynthia


use_xml_libname_engine.png

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: XML output

Yes. ods tagsets.excelxp

http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

although you can put xls or xlsx as the file type, it actually outputs xml file.

Haikuo

Solution
‎05-31-2012 11:23 PM
SAS Super FREQ
Posts: 8,866

Re: XML output


Hi:

  First you need to distinguish between what you want and what you are getting. I'm not clear on which is which. Possibly, I think that what you WANT is shown in the ZIP file and what you are actually getting is what you posted underneath your program???.

  Next, are you looking at your SAMPLE.XML file in Notepad or with a browser. If you look at your XML file in a browser, you will see a "collapse/expand" character (a plus ( + ) or minus ( - ) in a few places in the file. If you open the file with Notepad, or a regular ASCII text editor, you will not see the + or -.

  Then, are you sure that your program is producing the output that you pasted into your posting? I do NOT see how the XML Libname Engine would be giving a hypen or dash in the XML element derived from the SAS columns name. Especially since a hypen or minus or dash in a variable name (such as bank-code) is invalid syntax and your program shows a variable name of bank_code. You should be seeing <bank_code>...</bank_code>  (with an underscore, like in the variable name).

  You say you need to produce XML/HTML -- but, which do you need to produce? XHTML is a hybrid HTML markup language that conforms  to XML validity rules. But what you showed in your ZIP file was some type of XML file. I did not see ANY HTML tags in that file. If you need to produce an XML file from a SAS dataset, then you were on the right track using the SAS XML Libname Engine.

  ODS TAGSETS.EXCELXP does produce XML -- but not the type of XML that you showed in your ZIP file. ODS TAGSETS.EXCELXP produces Microsoft Spreadsheet Markup Language 2003 XML which does not look at all like what was in your zip file. Do you need to create Excel specific XML?

  For example, look at the attached screen shot. The root tag <TABLE> </TABLE> is constructed by the XML LIBNAME engine. It is the standard root tag used by the engine. The XML engine also inserts the XML tag at the top of the file (<?xml version="1.0" encoding="utf-8" ?>) This is the standard XML tag inserted by the LIBNAME engine.

  My sample dataset only had 1 observation. Because I called my dataset XMLOUT.FIIS, the "FIIS" data set name was used for the "observation" tag. If i had more than 1 observation, every obs would be delimited by an FIIS tag. But since I only have 1 obs, I only have 1 FIIS tag. If you wanted to change the <TABLE></TABLE> to something else, then you would have to use other techniques to change the root tag. The documentation for the XML Libname Engine talks about using either an XMLMAP file to do your export to a different structure, or you could construct a custom tagset template to use with the LIBNAME engine to change the TABLE tag to be something else. Or, you could post-process the XML file in order to get the - into the tags and to rename the TABLE tag to be something else.

cynthia


use_xml_libname_engine.png
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 384 views
  • 0 likes
  • 3 in conversation