SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

XML and DI Studio

Reply
Occasional Contributor
Posts: 9

XML and DI Studio

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
SAS Employee
Posts: 51

Re: XML and DI Studio

Can you post the actual XML please?

Thanks,

Tim Stearn
Occasional Contributor
Posts: 9

Re: XML and DI Studio

Posted in reply to TimStearn_SASProductManagement_
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
SAS Employee
Posts: 51

Re: XML and DI Studio

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

Re: XML and DI Studio

Posted in reply to TimStearn_SASProductManagement_
Hi

Anyone have any experience using XML and DI studio?

Thanks
Matt
Occasional Contributor
Posts: 7

Re: XML and DI Studio

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;
New Contributor
Posts: 4

Re: XML and DI Studio

Posted in reply to LaurentdeWalick
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.
SAS Employee
Posts: 75

Re: XML and DI Studio

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"
SAS Employee
Posts: 38

Re: XML and DI Studio

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.
Occasional Contributor
Posts: 9

Re: XML and DI Studio

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
Ask a Question
Discussion stats
  • 9 replies
  • 2027 views
  • 0 likes
  • 6 in conversation