Just as you can construct an XML document based on table data, you can also do the reverse by storing an XML document to a database. To store an XML document to a database, use the following procedure.
From Database Explorer under the ASE connection profile, expand the Procedures and Functions folder or, for triggers, expand the Tables folder and expand the table, then the Triggers folder.
Select the procedure you want to use to store XML data to the database. Right-click and select Edit Procedure. Alternatively, select the trigger you want to use to store XML data to the database. Right-click and select Edit Trigger.
The procedure or trigger code appears in SQL Editor.
Place the cursor in the desired location in the code, right-click and select Adaptive Server Enterprise | SQL/XML Wizard. You can also open the SQL/XML Wizard from the
icon on the toolbar. The Select a Wizard dialog appears. Select XML
to SQL Mapping functions.Modify the following input fields.
Choose the script type to be generated
Field Example Need to generate 'create' statement
Click this checkbox to generate a SQL create statement.
Need to generate 'insert' statement
Click this checkbox to generate a SQL insert statement.
XML error handling
Select an error-handling option from the menu dropdown. Options include exception, null, and message.
Click Next to continue.
Specify the necessary XML schema by modifying one of the following input fields.
Specify XML schema
Field Example Variable
Enter a variable in the text field, or choose an existing variable from the menu drop-down. Variable names begin with "@".
Document
Enter the content of the XML document in the text field, or choose Browse to point to an existing XML document.
Embedded expression
Enter an embedded expression in the text field.
Click Next to continue.
Specify the XML result set by modifying one of the following input fields. This step is optional.
Specify XML result set
Field Example Variable
Enter a variable in the text field, or choose an existing variable from the menu drop-down.
Document
Enter the content of the XML document in the text field, or choose Browse to point to an existing XML document.
Embedded expression
Enter an embedded expression in the text field.
Click Finish to create the XML-to-SQL mapping.
The SQL for the XML-to-SQL mapping is displayed in SQL Editor.