read XML all rows once

[Thokozani1]

Can you please assist.

Below is a sample from an xml we are trying to read. We don’t have the mapper for it and any other for that matter.

Currently we run a library pointing to the http file as below. We then run a proc dataset which gives us the datasets that are in the library. Each <item#> tags are basically rows which SAS interprets as a dataset. So if there are 1000 rows (<item0> to <item999>), it means there would be 1000 datasets to read, and hence means hitting the database a 1000 times. 

Could you please help us with a better way of reading this, hopefully a way that will allow us to read all rows once.

Libname wsdl xml92 "http://ncss/Webservice/capacity/method/xml/day/&DATE";

<?xml version="1.0"?>

<response><item0><Day>2016-10-06</Day><Period>0</Period><LoadForecast>0</LoadForecast><RevisedForecast>0</RevisedForecast><DayAheadAvail/><HydroAvail>0</HydroAvail><AdditionalHydro>0</AdditionalHydro><OCGTAvail>0</OCGTAvail><MaxAvail>0</MaxAvail><MinCapacity>0</MinCapacity><ActualSentout>22008</ActualSentout><RequiredInst>700</RequiredInst><RequiredReg>600</RequiredReg><RequiredTmr>700</RequiredTmr><RequiredInstActual>0</RequiredInstActual><RequiredRegActual>0</RequiredRegActual><RequiredTmrActual>0</RequiredTmrActual><DayAheadAdditionalSpinning>0</DayAheadAdditionalSpinning><TotalCurrentLoadLosses>-3503</TotalCurrentLoadLosses><TotalLoadLosses>-3503</TotalLoadLosses><TotalUnplanned>-665</TotalUnplanned><TotalAdditional>1600</TotalAdditional><TotalDeviation>-2568</TotalDeviation><TotalUnitDeviations>-665</TotalUnitDeviations><OperatingRsv>-2568</OperatingRsv><Surplus>-2568</Surplus><Colour>Red</Colour></item0><item1><Day>2016-10-06</Day><Period>1</Period><LoadForecast>0</LoadForecast><RevisedForecast>0</RevisedForecast><DayAheadAvail/><HydroAvail>0</HydroAvail><AdditionalHydro>0</AdditionalHydro><OCGTAvail>0</OCGTAvail><MaxAvail>0</MaxAvail><MinCapacity>0</MinCapacity><ActualSentout>21684</ActualSentout><RequiredInst>700</RequiredInst><RequiredReg>600</RequiredReg><RequiredTmr>700</RequiredTmr><RequiredInstActual>0</RequiredInstActual><RequiredRegActual>0</RequiredRegActual><RequiredTmrActual>0</RequiredTmrActual><DayAheadAdditionalSpinning>0</DayAheadAdditionalSpinning><TotalCurrentLoadLosses>-3518</TotalCurrentLoadLosses><TotalLoadLosses>-3518</TotalLoadLosses><TotalUnplanned>-665</TotalUnplanned><TotalAdditional>1600</TotalAdditional><TotalDeviation>-2583</TotalDeviation><TotalUnitDeviations>-665</TotalUnitDeviations><OperatingRsv>-2583</OperatingRsv><Surplus>-2583</Surplus><Colour>Red</Colour></item1> </response>

Regards,

Thokozani

[Ksharp]

Once you got these tables, you can combine them all together into a table.

filename x temp;
libname xx xmlv2 '/folders/myfolders/temp.xml' xmlmap=x automap=replace;

data want;
 set xx.item: ;
run;