I have some XML data which I'm trying to read into SAS 9.1.3 through the XML libname engine. Said XML data looks a lot like this (some may recognise that's it's output from PROC METADATA):
<?xml version="1.0" encoding="windows-1252"?>
<GetMetadataObjects>
<Reposid>A0000001.AREPOSID</Reposid>
<Type>Person</Type>
<Objects>
<Person Id="AREPOSID.AA000001" Name="User The 1st">
<Logins/>
</Person>
<Person Id="AREPOSID.AA000002" Name="User The 2nd">
<Logins>
<Login Id="AREPOSID.AV000001" UserID="AUserID">
<Domain>
<AuthenticationDomain Id="AREPOSID.AD000001" Name="DefaultAuth"/>
</Domain>
</Login>
<Login Id="AREPOSID.AV000002" UserID="AlsoUserID">
<Domain>
<AuthenticationDomain Id="AREPOSID.AD000002" Name="DummyAuth"/>
</Domain>
</Login>
</Logins>
</Person>
<Person Id="AREPOSID.AA000003" Name="Person The 3rd">
<Logins/>
</Person>
</Objects>
<NS>SAS</NS>
<Flags>260</Flags>
<Options>
<Templates>
<Person Id="" Name="">
<Logins Id=""/>
</Person>
<Logins>
<Login/>
</Logins>
<Login Id="" Userid="">
<Domain/>
</Login>
<Domain>
<AuthenticationDomain/>
</Domain>
<AuthenticationDomain Name=""/>
</Templates>
</Options>
</GetMetadataObjects>
The above XML is associated with a file reference called xml_out, and I'm using the following code to read it into a SAS dataset:
filename xml_map temp;
data _NULL_;
file xml_map;
put
'<?xml version="1.0" encoding="windows-1252"?>'
/ '<SXLEMAP xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="TreeMap" version="1.2" xsi:noNamespaceSchemaLocation="http://www.sas.com/xml/schema/sxle12.xsd">'
/ ' <TABLE name="user_logins">'
/ ' <TABLE-PATH syntax="XPath">/GetMetadataObjects/Objects/Person/Logins/Login</TABLE-PATH>'
/ ' <COLUMN name="UserMetaId">'
/ ' <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/@Id</PATH>'
/ ' <TYPE>character</TYPE>'
/ ' <DATATYPE>string</DATATYPE>'
/ ' <LENGTH>200</LENGTH>'
/ ' </COLUMN>'
/ ' <COLUMN name="UserMetaName">'
/ ' <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/@Name</PATH>'
/ ' <TYPE>character</TYPE>'
/ ' <DATATYPE>string</DATATYPE>'
/ ' <LENGTH>200</LENGTH>'
/ ' </COLUMN>'
/ ' <COLUMN name="LoginsGroup">'
/ ' <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/Logins/@Id</PATH>'
/ ' <TYPE>character</TYPE>'
/ ' <DATATYPE>string</DATATYPE>'
/ ' <LENGTH>200</LENGTH>'
/ ' </COLUMN>'
/ ' <COLUMN name="LoginDomain">'
/ ' <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/Logins/Login/Domain/AuthenticationDomain/@Name</PATH>'
/ ' <TYPE>character</TYPE>'
/ ' <DATATYPE>string</DATATYPE>'
/ ' <LENGTH>200</LENGTH>'
/ ' </COLUMN>'
/ ' <COLUMN name="LoginId">'
/ ' <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/Logins/Login/@Id</PATH>'
/ ' <TYPE>character</TYPE>'
/ ' <DATATYPE>string</DATATYPE>'
/ ' <LENGTH>200</LENGTH>'
/ ' </COLUMN>'
/ ' </TABLE>'
/ '</SXLEMAP>'
;
run;
libname xml_out xml xmlfileref=xml_out xmlmap=xml_map;
data logins_details_test;
set xml_out.user_logins;
run;
The output data from this procedure looks like this:
UserMetaId UserMetaName LoginDomain LoginId
---------------------------------------------------------------
AREPOSID.AA000002 User The 2nd DefaultAuth AREPOSID.AV000001
DummyAuth AREPOSID.AV000002
Can anybody suggest what I'd need to change in either the xml map, or in the libname statement so that I get either 1 row per login found, or a single row with no login details if no login details are visible? i.e.
UserMetaId MetaName LoginDomain LoginId --------------------------------------------------------------- AREPOSID.AA000001 User The 1st AREPOSID.AA000002 User The 2nd DefaultAuth AREPOSID.AV000001 AREPOSID.AA000002 User The 2nd DummyAuth AREPOSID.AV000002 AREPOSID.AA000003 User The 3rd
