Version 2.1.11
OS Windows and Linux
Challenge

Store Agent Output in the Ba-PRO repository


Step 1: Create XML file with SQL Agent/SAP Agent

Step 2: Create XSD (Schema Definition) for XML file from the step 1

The XSD file is used for description of data that were extracted by the agent and placed in xml file.
You can generate a valid XSD file with any XML tool such as XMLSpy:

Open the XML file and then go to menu DTD/Schema àGenerate DTD/Schema

For xsd there are 3 types of elements:


Note:
Oracle has some specialities regarding XSD files. You must define

If you are using XMLSpy simply go to Schema design view;
For each node from XML that has others nodes inside it has to be added an element of complex type: Add complex type
It is named usually as the xml element and for this one you must add a sequence to add the other elements:
Right Click on the element : Add child Sequence

Add child Add element

Into one complex element from xml file you may find many times the same elements.
There must be defined the cardinality for these one.

Right click on the element and select: Unbounded

Each xml element may have defined an attribute: in attribute window select :
Add Attribute – named as it is in the xml file and chose the type String, used mode(Optional, Requested, Restricted) usually is the requested type.

Test your schema : DTD Schema àGenerate Sample XML , the xml file generated must look as the agent xml.

For ORACLE these schema it has to be saved as fallows:

  1. Activate option Schema Design:àEnable Oracle Schema Extensions
  2. go to root element and into Details section at Oracle add one name in filed deftable and save it.

Step 3:Copy the created XSD file to the WEBDAV directory of the database via ftp. Use any browser for that


Step 4:Open a database editor

Such as SQL developer, TOAD or HORA or any other db tool and

Go to SQL section– and write the command:

BEGIN
DBMS_XMLSchema.registerSchema(
schemaurl=>'http://192.168.0.223:8181/test/testV1.xsd',
schemadoc=>sys.UriFactory.getUri('/test/testV1.xsd'));
END;
/

Execute by selection of it and Ctrl+E: the result is one table named as you defined in field deftable

Possible Test:

In XML SPY to the root element of XML modify at :

xsi:NamespaceSchemaLocation=”.....path to xsd created and pasted on DB into ftp

Example: http://192.168.0.223:8181/test/testV1.xsd

Press validate and the program shows you any validation errors (e.g. no attributes defined etc.)
If it is ok also for test take the XML file saved with the above path, and copy into the DB on the ftp, at refresh if the xml file dimension is 0 byte is ok and file.
In case of non valid file at refresh the xml file may be automatically erased or the dimension is other than 0 bytes.

Note:

XSD order to register into the BD:

  1. copy xsd on ftp
  2. run command of registration (begin….)
  3. unregister the XSD

ad 3) unregister the xsd:

a) run the the command below:

BEGIN
DBMS_XMLSchema.deleteSchema(
schemaurl=>'http://192.168.0.223:8181/test/testV1.xsd',
delete_option = > 4
);
END;
/

b) delete xsd file from the db (from ftp)

There you must register the new xsd.

Step 5: Modifying the configuration files of agent

To the configuration file of the agent ex:”ssss.ini” at field OutputSchema write the reference path =”.....path to xsd created and pasted on DB into ftp

Example: http://192.168.0.223:8181/test/testV1.xsd

To StorageClass Config – you have the path to the configuration file on the ftp (ex:”webdav.ini”) were it tells the agent what is the path for putting the xml file (modify this with the one you desire)


Step 6: Implementation of Business Rule with XQUERY using XML and XSD