Desktop productivity for business analysts and programmers

How to avoid the root element <ROWSET> in an xml file to be replace by <TABLE>

Reply
Regular Contributor
Posts: 168

How to avoid the root element <ROWSET> in an xml file to be replace by <TABLE>

Hello,

 

I am converting a dataset into an XML file using EG 7.11.  I have noticed that even if I use xmltype=oracle, when I add a tagset conditions, it replace <ROWSET> for <TABLE>.

 

Here's my code.  In the first example, I don't use any tagset condition so the root element <ROWSET> remains in the xml file.

However, when I add any tagset condition, the root element is replaced for <TABLE>.

 

How can we avoid that?

Regards,

 

 

%let Path=\\...;
libname trans xml "&Path.\Documents\Test\XML\class.xml" xmltype=oracle;

data trans.class noprint;
   set sashelp.class;
run;
libname trans xml "&Path.\Documents\Test\XML\class.xml" xmltype=oracle
tagset=tagsets.sasxmnsp;

data trans.class noprint;
   set sashelp.class;
run;

Frequent Contributor
Posts: 139

Re: How to avoid the root element <ROWSET> in an xml file to be replace by <TABLE>

Trying to get exactly what one wants from SAS tagsets has proven, at least for me, to be a difficult task.  What I've done is to write post-processing programs that make some final adjustments to the XML, adjustments I just couldn't figure out how to get SAS to do.

 

Here's an example.  This post-processing program does two things:  1.  It changes Numeric definitions to Text (so I don't lose leading zeros and such) and it deletes rows after a "MergeAcross".  What I want is to merge several columns on one of the rows.   SAS generates the correct XML in the merged columns... but then writes out the very columns it was supposed to merge.  I could not figure out how to get SAS to write the XML correctly, so I just wrote a quickie program to get rid of the extraneous columns myself.

 

Below is the code.  Hopefully you can do something like the below to get what you need.  Trying to get SAS to produce exactly what you need in terms of XML is like trying to scratch your ear with your elbow.

 

Hope this helps,

 

Jim

 


*------------------------------------------------------------------------------------------------*;

FILENAME	XML_In			"&Output_Path/Temporary_Work_File.Txt";
&NoMacs		%Error_Check	(MsgLvl=&MsgLvl, ErrLvl=&ErrLvl);

FILENAME	XML_Out			"&Output_Path/&Fro_Pro_File";
&NoMacs		%Error_Check	(MsgLvl=&MsgLvl, ErrLvl=&ErrLvl);

**	Post process the xml, deleting cells after a MergeAcross.	**;
DATA	_NULL_;
	RETAIN	_First_Time			1;
	RETAIN	_Delete_Records		0;

	IF	_First_Time									THEN
		DO;
			_First_Time						=	0;
			SYSECHO	"Post processing &Fro_Pro_File";
		END;

	INFILE	XML_In
		LENGTH								=	_SAS_Length
		;

	INPUT	@1	_Entire_Record	$VARYING32767.	_SAS_Length;

	IF	MISSING(_Entire_Record)						THEN
		DELETE;

	_Entire_Record	=	TRANWRD(_Entire_Record,'<Data ss:Type="Number">','<Data ss:Type="String">');

	FILE	XML_Out;

	IF	INDEX(_Entire_Record,"MergeAcross")	>	0	THEN
		DO;
			_Delete_Records					=	1;
			PUT	@1	_Entire_Record	$VARYING32767.	_SAS_Length;
		END;
	ELSE
	IF	_Delete_Records								THEN
		IF	INDEX(_Entire_Record,"</Row>")	>	0	THEN
			DO;
				_Delete_Records				=	0;
				PUT	@1	_Entire_Record	$VARYING32767.	_SAS_Length;
			END;
		ELSE
			DO;
				DELETE;
			END;
	ELSE
		DO;
			PUT	@1	_Entire_Record	$VARYING32767.	_SAS_Length;
		END;
RUN;

*------------------------------------------------------------------------------------------------*;

Ask a Question
Discussion stats
  • 1 reply
  • 56 views
  • 0 likes
  • 2 in conversation