Storing xs:any fields via DSP - Data Service Integrator

Hello everyone, I'm glad to see DSP back in business :)
In a project for a customer we're preparing order management system, used to perform process operations on ALSB/DSP sets of services. It is based on WLI/ALSB/DSP/ORA infrastructure.
Order schema is composed of few typed elements (orderId, orderDate, and so on) and one untyped (xs:any) orderHeader element - for storing xml document of unknown structure (in fact, structures are known, but very different for different orders), mapped to database type of CLOB (or very large string).
There comes the question: how can we perform insert / update operations via DSP on such a structure, WITHOUT losing the xml markup in the orderHeader field after storing in Oracle? What we have now is working physical and logical services, with one glitch: <orderHeader><test><field1>1</field1><field2>2</field2></test></orderHeader> get inserted and updated like this: ORDER_HEADER = "12".
Anyone, any ideas how to push the XML markup through the service?
Best regards,
Norbert 

fn-bea:serialize($order) will string-ify your Order element and you can store that in a CLOB/String. And to turn the string into typed XML use validate{ fn-bea:inlinedXML( $string )  } (you will need to have a schema matching that type in your dataspace else validation/typing will fail).
However - you might want to revisit your architecture. XML stored in database strings is inefficient. It would be difficult to use such a column as an index, or to even perform a simple selection on it (for ORDER_ID=123, for instance - you would have to hope that <ORDER_ID>123</ORDER_ID> didn't occur in a product name within the order. To select on any XML would require DSP reading all the rows in that table, turning them into XML and then evaluating the selection. 

Thanks Michael, that's exactly what I was looking for.
As for your remarks for the efficiency of the XML storage in strings and later querying them - you're absolutely right, and hopefully we haven't made it THAT bad :)
The order XML document (as well as order lines XML documents in order line details table) is accompanied by decent set of indexed fields (integers, datetimes, etc.). The content of the document can also be indexed by set of key / value pairs in external table. Keeping the orders XML in string is fair enough, as long as the documents have to be "understood" only by dedicated WLI processes, after being retrieved from DB by DSP.
Thanks again,
Norbert

Related

mapping rich XML to a single table

In order to support searching and update of documents using plain SQL (a customer requirement), I want to map a rich (multilevel) XML data structure to a single relational table with a structure somewhat like
(ID varchar2(4),
Element_Name varchar2(20),
Element_Data varchar2(20))
I'll use a dot convention for nesting the structure so subelements will have names like:
"subelement.dataelement1"
"subelement.dataelement2"
...
Problem is this requires some work to turn it back into XML and I cannot use default tools to import and export the results.
Is there a better way to do this?
I want to be able to retrieve xml documents of the form:
<DATAELEMENTNAME>value</DATAELEMENTNAME>
where DATAELEMENTNAME is held in one col of the table and value is held in another col. BUT must be able to support value lists in the resulting document. This will allow a flexible document format while leaving the data searchable and modifyable using standard SQL. The data is mainly of the form
name1:value1
name2:value2
I can afford to store all the values as text and convert later to numbers, dates as required. 
I have designed a simple relational table sturcture that can handle XML, in 4 brief tables. Email me jwagner#emory.edu if interested.
null

Plain xml processing using xml schema

Hi everyone. I got some questions about working with xml and xsd in Oracle Database. Is there any way in Oracle to work with xsd without xdb? For example, I got a very plain xml, all elements are located under root, no hierarchy at all. And I got an xsd, containing all datatypes in this xml. To be specific, let it be<Elements> <Element>   <id>1</id>   <name>First</name> </Element> <Element>   <id>2</id>   <name>Second</name> </Element></Elements> <xs:element name="Element">  <xs:complexType>    <xs:sequence>      <xs:element name="id" type="xs:integer"/>      <xs:element name="name">        <xs:simpleType>          <xs:restriction base="xs:string">            <xs:maxLength value = "100"/>          </xs:restriction>        </xs:simpleType>      </xs:element>    </xs:sequence>  </xs:complexType></xs:element> Pretty simple.  All I need to do is to select those fields from XML as an ordinary table rows with corresponding data types.What do I do now:SELECT msg.* FROM tbl,XMLTABLE ('/Elements/Element' PASSING tbl.xml COLUMNS   "id" NUMBER PATH 'id' , "name" VARCHAR2(100) PATH 'name') msg; This has nothing to do with XSD. If XML structure is changed, XSD is changed, I can not just update XSD in my table and use it, I should go to all my code and change everything. It is very unmaintainable. Is there any function in Oracle like XMLTABLE just with additional parameter - XSD data, so I do not need to hardcode all of that stuff?  Thanks for reply.
I should go to all my code and change everything. It is very unmaintainable.
That's your point of view.Of course, I agree that changing hardcoded stuff all over the code is just a pain in the a**.That's why such things have to be modularised as much as possible. And in the present case (ie XML processing in Oracle), hardcoding XQuery is a prerequisite for performance. 
Is there any function in Oracle like XMLTABLE just with additional parameter - XSD data, so I do not need to hardcode all of that stuff?
No, there is no such thing.All you can do is omit the datatype and PATH expression, when the XML is stored in a schema-based XMLType column/table, they will be infered from the schema. Let me ask you a question in return :How do you think that hypothetic function would work? How would it know which XPath to query, which element/attribute values to project?An XML schema just describes data and its structural aspect, it can't decide which information to extract and how to present it. Your sample XML is rather simple indeed. Could you give an example of a typical change that could occur on the structure?
Ok, as for requirements. Function input parameters: xmltype with xml data, varchar2 with starting xpath (xpath for row elements), and optional xmltype with xsd in case xml is simple enough and one xsd is sufficient. If no xsd is provided, or it is not sufficient, then function looks into xdb. Algorithm: First, we got starting element - our row, we go to xsd and look for all possible elements inside that row and their datatypes. We are creating a row with corresponding columns and types, and fill them with null values. Then we are processing our xml element tag by tag, when we got a value, we substitute it into our row. Next element - next row and so on. That is good for plain xml. What we are going to do with complex types, you'd ask? Ok, there are two variants, as I can see. First one is simple and starightforward. We just create an xmltype column in our row and put that element in it completely as it is.Or we can use oracle's nested tables, so we go recursively, parse that complex type with exactly same algorithm and put into column as nested table.We could even combine both approaches, adding one more parameter to the funtion - parse depth. That could be realized, I think. Perfomance with big data would be terrific, but actualy when it goes to xml, perfomance is no more anyway.
Thanks for elaborating.If no xsd is provided, or it is not sufficient, then function looks into xdb.What do you mean "look into xdb"? Look for what exactly?We are creating a row with corresponding columns and types, and fill them with null values. Then we are processing our xml element tag by tag, when we got a value, we substitute it into our row. Next element - next row and so on.So you would be building a dynamic resultset on the fly, with an unknown number of columns at design time?How do you plan on handling this resultset in your code? Dynamic SQL and PL/SQL everywhere? Not quite the expected maintainabilty IMO.A more concrete example : Say the XML structure evolves and now each "row" has a new "column" (ie a new child element).Q: How does your code automagically know what to do with this new column? Does it have the necessary structure (variable, table column, record field etc.) to hold the value?I suggest you rethink again on what you're asking and what it implies in real production code.What you described about more complex structures is already implemented by Oracle actually, that's called Object-Relational storage. You can read about it in the manual if you're not familiar.But again, you have to know (hardcode) the xpath/xquery you want to query, seems like common sense to me.but actualy when it goes to xml, perfomance is no more anyway.What a definitive statement... Work a little bit more with Oracle and XML DB, read some docs, and maybe you'll learn what you can do. Regards.
Thanks for discusing. Yeah, resultset is dynamic, and this is the only reason I'd use xml, not plain tables.What I'm trying to realize is an rule engine. I got xml on input, I got some rules, and I wand an xml on output.Input xml is fixed and specified with xds, but there are hundreds of them. Now for each type I got a table and parse xml into it with XMLTABLE like in the first post. Then I do some processing and generate resulting xml with DBMS_XMLGEN. I want to simplify this and make uniform processing, so I got minimum hardcoded elements. And I was curious is there something in oracle already, which might help me. As far it seems that there is not. I would read about xdb, but it seems too monstrous and overkill for my task from a bird's eye view.
It's getting interesting. 
I want to simplify this and make uniform processing, so I got minimum hardcoded elements.
So I guess it's more an issue of designing a common structure to hold XML data. Could you please share a more detailed example? Sample input, kind of transformation to perform, sample output required?

Updating a XML stored in BLOB column of a table

I have a table which contains XML files stored in a BLOB Column. I want to do the following. Is any one can help?
1. The xml file have a node / element Status. I want to update the status.
Example:
<Status>SUBMIT</Status>
2. If the node / element having multiple lines ie., subnodes / sub elements, I want to update a particular sub element. (The sub element name are same or different)
Example1: I want to update third line. ie., 48033 to new value
<Address>
<AddressLine>21557 TELEGRAPH ROAD</AddressLine>
<AddressLine>SOUTHFIELD MI</AddressLine>
<AddressLine>48033</AddressLine>
<AddressLine>UNTD STATES</AddressLine>
</Address>
Example2: I want to update the line PartPrefix. ie., RAJ1 to new value
<ItemNumber>
<ItemId>RAJ1-54603C32-BL</ItemId>
<PartContractBase>5460332</PartContractBase>
<PartPrefix>RAJ1</PartPrefix>
<PartBase>54603C32</PartBase>
<PartSuffix>BL</PartSuffix>
</ItemNumber>
3. If the xml file having any special characters, it is not updated the correct modified xml into the BLOB column. 
You could convert the BLOB to an XMLTYPE, which would give you some methods for doing this. Then you convert the XMLTYPE back to a BLOB and update the table.
However, this is terribly inefficient, and not a good idea if there are a lot of rows to be changed, and it isn't a one-time change. The real questions to ask:
Should you be storing this data as an XMLTYPE rather than BLOB? That would eliminate some of the steps above.
Should you be parsing the XML and storing it in relational, or object/relational tables and columns? That would be fastest in the long run. If you look at XML DB, you will find that if you have an XML schema for these files, you can get it to automatically parse the data into object/relational tables or object views of relational tables. 
The file is stored in the table as a BLOB column not as XMLTYPE. Also we are not parsing the XML and just storing it in a column. We just get the file from table and update some nodes and update back into the table. 
Hi,
I had a similar question once to which I got [a really good answer|http://forums.oracle.com/forums/thread.jspa?messageID=2547517#2547517]
Hope this helps
Peter 
Listen, if you are updating a node in some XML, the XML has to be parsed to do it. You might not be doing it directly, but some piece of code that you are invoking has to parse the XML.
Which brings me to: Hey folks, XML is cool, it's a great way to transfer information from one place to another. It's not a bad place to archive data. But it is a terrible place to store active data if you need to read and write individual elements. That is what a relational database is for.
Peter, the answer you got very clearly states that it is good for a one-off - one time use. But it is going to be slow if you have to do it over a large amount of data, or do it regularly as part of an active application.

Retrieve XDB$ENUM_T Text Values not index

All,I have an xml element of type xdb$enum_t where I cannot seem to figure out how to fetch the text associated with the index value. The only way I seem to be able to get to the index value is through the object relation dot notation (.value). This gives me the index value of the restriction list. I saw one other post referring to the same issue but was never answered. I need this xml element restricted by this list. Any help or ideas appreciated. I am using Oracle 11g. Thanks,Tom
Hi Tom, It's not the right approach for whatever you're trying to achieve.XDB$ENUM_T is internal stuff which is not meant to be manipulated directly.Always use XQuery/XPath functions to access node values. 
I need this xml element restricted by this list.
What do you mean?Are you using Object-Relational storage via an XML schema? If so, what's the problem? Could you post a small test case? Thanks.
Odie_63, Thanks for the reply. Yes you are correct with the use of the XDB$ENUM_T. I was using an XML element restricted by an enumerated list. When I designed my XSD using XMLSpy product I did not typecast the ORACLE variable it would be placed into. XDB document shows that enumerated data type by default would be cast to this datatype. When you would retrieve this value using object relational notation you would only get the index value not the actual value of the element. This solution was called out in the posting here.   http://www.serkey.com/oracle-tips-for-enumeration-bdrfzv.html Once I typecast to VARCHAR2 everything worked as it was intended. The value is correctly returned in the object relational notation. I hope this posting help other people encountering this problem because it took me 2 full days to fix this problem. Thanks,Tom
The value is correctly returned in the object relational notation.
Still, accessing the data this way is not supported (unless you want to create an index for example) : XML Schema Storage and Query: Basic

How to force simple tags and null attributes to appear when using SQL/XML?

Hello everybody:
I'm developing a non-schema based XMLType view.
When the XML document is generated, i noticed two things I need to manage in order to achieve the desired result:
1. Oracle generates a <tag></tag> pair for each XMLELEMENT defined; in my case, some tags need to appear as <tag/>... how do I do? Is it possible when using schema based XMLType views? Is it possible while using a non-schema approach?
2. When using XMLATTRIBUTE('' AS "attribute") or XMLATTRIBUTE(NULL AS "attribute"), no one attribute with label "attribute" and null value appears at the output; how do I force to Oracle DB to render those attributes which are with no values (needed to render those attributes as another parsing code will await for all the items)?
3. Some tip about how to route the output to an XML text disk file will be appreciated.
Thanks in advance.
Edited by: Enyix on 26/02/2012 11:21 PM
Edited by: Enyix on 26/02/2012 11:22 PM 
Hi,
Oracle generates a <tag></tag> pair for each XMLELEMENT defined; in my case, some tags need to appear as <tag/>Both forms are semantically equivalent. Why do you need the short form?
The appearance of empty tags is controlled when the XMLType is serialized (into a CLOB, VARCHAR2 or BLOB).
You can use XMLSerialize with INDENT option to generate the short form, but then the result won't be an XMLType anymore.
When using XMLATTRIBUTE('' AS "attribute") or XMLATTRIBUTE(NULL AS "attribute"), no one attribute with label "attribute" and null value appears at the output; how do I force to Oracle DB to render those attributes which are with no valuesIt is expected behaviour. XMLAttributes doesn't create attributes with NULL content.
You can achieve what you want with XQuery though.
Are you selecting from multiple base tables?
Some tip about how to route the output to an XML text disk file will be appreciated.DBMS_XSLPROCESSOR.CLOB2FILE
A small example here :
http://odieweblog.wordpress.com/2011/11/23/how-to-write-xml-to-file-with-encoding-specification/ 
Hello odie_63, thanks for your reply:
Reasons why needed single tags are these two next: Needed to generate a single XML file from 50,000,000 rows, where the XML ouput matches not only row data but another default values for another elements and attributes (not from database but using strings and types with default values); by using start and end tag, the generated file is as much twice bigger than using single tags; second, needed a very precise presentation for all the document.
For generating that document, currently focus is based on using a batch process relying on Spring Batch with using a single JDBC query where a join happens between two tables. From my point of view, that approach uses: database resources, network resources, disk resources, and processing resources, including the price of making the join, sending to network, creating objects, validating, and making the file (Expending too much time generating that XML file). That processs currently is in development.
I think possibly another approach is delegating the complete generation of that file to the database using its XML capabilities. My current approach following your recomendations is to generate a clob where I will put all the XML and putting it into a table. It leads me to another issues: Considering limitations on memory, processing and disk space, needed to append a single row-as-xml into the clob as soon as possible, and putting the clob inside the field as soon as possible, or putting the clob inside the field, and appending into it as the data is generated; so How do I manage the process in order to achieve that goals?. Seen these issues aren't related to my original question, so I'll open a new post. Any help will be apreciated.
Thanks again in advance.

Categories

Resources