XML and DI Studio

[matt_w]

Hi

I've been experimenting with loading and writing to XML files in DI studio .
The example I tried had a structure as below

Header
>Body
>>Table1
>>Table2
>>Table3
Trailer

I created an XML library that referenced the XML file, and attempted to register the tables. When I first tried I could only see the Header, Body and Trailer as SAS objects; in order to get Table1, 2 and 3 I had to edit the XML file to delete all the information relating to the Header, Body and Trailer.

Is there a way to see all objects in an XML file with a hierarchy such as this?

Thanks
Matt

[TimStearn_SASProductManagement_]

Can you post the actual XML please?

Thanks,

Tim Stearn

[matt_w]

Apologies - should have done this initially.

So, my original data has the structure of the example below.


-
-
MHHDR0304
123456789012345
260000001200001
IC MH
0000001
2010-02-08
13:07:00
0

-
-
1
XY
ABC
1970-01-01
1970-01-01
ABCDEF
DEF
2
P
1970-01-01
ABCDEF
GHI
ABCDEF
B
1970

-
10001
01
1970-01-01
092
0
0
0
0
0
0
0
0

-
000
000
000
000
000
000
000
003
000
000
000
000
000
AB99 1XY
000
000
000
000
000
E

-
2
XY
ABC
1970-01-01
1970-01-01
99AABCD
GHI
1
D
1970-01-01
ABCDEF
GHI
ABCDEF
A
2006

-
10002
01
1970-01-01
092
0
0
0
0
0
0
0
0

-
000
000
000
000
000
000
000
003
000
000
000
000
000
AB99 1XY
000
000
000
000
000
E


-
MHHDR0000
123456789012345
260000001200001
IC MH
0000001
55



I've used an XML library linked to this file and see that I can register the Header, Body and Trailer tables but cannot see the Table_1, _2 and _3 within the Body.

I've amended the XML as below to get rid of Header, Body and Trailer and successfully register Table_1, _2 and _3.


-
-
1
XY
ABC
1970-01-01
1970-01-01
ABCDEF
DEF
2
P
1970-01-01
ABCDEF
GHI
ABCDEF
B
1970

-
10001
01
1970-01-01
092
0
0
0
0
0
0
0
0

-
000
000
000
000
000
000
000
003
000
000
000
000
000
AB99 1XY
000
000
000
000
000
E

-
2
XY
ABC
1970-01-01
1970-01-01
99AABCD
GHI
1
D
1970-01-01
ABCDEF
GHI
ABCDEF
A
2006

-
10002
01
1970-01-01
092
0
0
0
0
0
0
0
0

-
000
000
000
000
000
000
000
003
000
000
000
000
000
AB99 1XY
000
000
000
000
000
E



My question is (and apologies if this is down to my lack of understanding of XML) - can I use a SAS XML library to register Tables_1, _2 and _3 without having to edit the XML? Or, is there some other way of using SAS to achieve this?

I'm using SAS DI studio 4.21 on Windows.

Thanks again.
Matt

[TimStearn_SASProductManagement_]

I'll have time to look into this on Monday and I'll get back to you. Sorry for the tardy response.

[matt_w]

Hi

Anyone have any experience using XML and DI studio?

Thanks
Matt

[LaurentdeWalick]

You need to create a XML map using the XML mapper. I included a shortened version for your XML below.




/MHMDSMesage/MHMDS_BODY/Table1

/MHMDSMesage/MHMDS_BODY/Table1/Field_01
numeric
integer


/MHMDSMesage/MHMDS_BODY/Table1/Field_02
character
string
2



/MHMDSMesage/MHMDS_BODY/Table_2

/MHMDSMesage/MHMDS_BODY/Table_2/Field_01
numeric
integer


/MHMDSMesage/MHMDS_BODY/Table_2/Field_02
numeric
integer





Add the XML map to your XML libname statement.

filename foo 'D:\foo.xml';
filename foomap 'D:\foo.map';
libname foo xml xmlmap=foomap access=READONLY;

[Jo_SAS]

Hi Laurent,

With respect to the your response, Could you please provide me the best way to incorporate this into SAS DI studio.

I mean, do you want me to create a user define transformation to use the below code

filename foo 'D:\foo.xml';
filename foomap 'D:\foo.map';
libname foo xml xmlmap=foomap access=READONLY;

Or

is there any alternate solution to read both xml file and xml map files into SAS DI? I want to standardize to read XML data into SAS DI studio.

Kindly provide your response.

Thanks in advance.

[DaveR_SAS]

The DI Studio 4.21 user guide has a topic that might be relevant. The topic is mainly about how to create a web service job, but it includes some subtopics about XML inputs and outputs in jobs that might be relevant to your case:

http://support.sas.com/documentation/cdl/en/etlug/62233/HTML/default/n11c9v52jdl3ccn1r9niaolp1p2y.ht...

See "Create XML Libraries for the Inputs and Outputs" and "Register the XML Inputs and Outputs"

[nar_sas]

DI Studio can read and write XML files directly. To read the XML file, you specify the XML map in the libname statement, and then point the library to an XML table that is readable with the map. To write XML files, in the access folder is an XML Writer transform that can either use a map or a template file to format the XML when writing it back out. You can see an example template file in the pre/post code tab of the XML writer transform if you want to see an example. We included it in the pre/post code so that users would have an example to start from.

[matt_w]

Hi

Apologies for my recent absence, and thanks to those who've replied.

I've had XML mapper installed and have created a map file from my XML. I can view the data in a tabular form in the XML map application and can see that the example code generated in XML mapper follows the format Laurent de Walick describes earlier in this thread.

But, as Jo@SAS notes, I want to find the best way to incorporate this into DI studio.

I think the way to do it is via a library and created one which references the XML source file. I leave the XML type field blank, and on advanced options > XML Map tab navigate to the XML map I created using XML mapper.

This creates OK, but when I try to register the tables I get an error - "No tables were retrieved from your query. Your connection information may be incorrect."
When I look at the log I see that the libref was succesfully assigned but then get a partial message "encountered during XMLMap parsing occurred at or near line 16, column 36".

Anyone know what I'm doing wrong? any other options on the library, or is there some other way of using the XML map within DI studio?

Thanks again.
Matt

[ArunSingapore]

Hi Matt,

Did you found a best solution to read xml using SAS DI?
i am also facing same issues..not able to find a best solution except using xml libname...Thanks

[Rama_V]

Hi Matt,

I am not sure the structure of your XML file properly. As per previous comments you need XML map and that can be created using XML mapper very easily and also you can move the data in the tags into another table.

In short, you need XML map to register XML library for your XML file. If you have three tables properly placed in the XML file then you are able to register three tables into metadata easily. I have done this using 4.7 DI studio and used XML V2 type template. I am not sure about limitations with 4.2 DI Studio. Sure it will work.

Below link gives the some information around XML structure and XML map.

https://blogs.sas.com/content/sgf/2016/06/24/tips-for-reading-xml-files-into-sas-software/

Kind Regards,

Rama

[ArunSingapore]

Thanks a lot for the quick response.
Let me explain the background.

We were using Talend to ingest xml files to MS Sql server from here we were
reading in SAS Eminer to build models.

Myself only suggested client,we can use SAS DI it is powerful DI tool.Now
client is asking why cant we use just a transformation to read xml file
like in talend,in talend ita simple..we use one transformation tInputXML
and configure settings.

My client is expecting same process,he do not want to write code.

Thanks
Arun
+65-91286603

[Rama_V]

I didn't write any code at all. XML Mapper auto generates the XML Map file for your XML file, as along structure is same there is no need to touch the XML map.

DI Studio represents that XML file as table in metadata and use Table loader to load into SQL Server directly. No code. 

I do not have experience in Talend but I believe it is similar except Map file is generated inside the Talend tool. when XML file structure changes then you still need readjust the XML Map transformation according to new structure in Talend .