Generating views XSD based,

Question

Hi,

We would like to store xml (typed) in SQL (as xml datatype). I have created a column of type xml and created a schema. Inserting xml data works great. Now I only need to retrieve the data. Could someone put me on the right trail:

1) I'd like to minimize the use of the rather complex xquery paths.. I think creating a view for this purpose. I'd think there must be a tool to generate views based on the xsd. Or is there perhaps another way to query this data omitting xquery..???

2) How can I handle complex XML structures with repeating elements in queries? Is there any document describing this complexity?

Ok thanx for you time

Sander

Answer 1

I don't see the advantage in a mixed XML/relational approach. Either use XML or relational data types.

Answer 2

Hi Sander,

The following article maybe helpful to you to using XML data type in SQL Server:

SQL Server XML Performance Tips
Working with XML Data in SQL Server 2005

---

Best Regards,
Stephanie Lv

Forum Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [e-mail].

Answer 3

Hi Saaffy,

metadata about XML SCHEMA COLLECTION is stored in SQL Server views, eg

SELECT *
FROM sys.xml_schema_collections

SELECT *
FROM sys.xml_schema_elements 

It would be possible to query this and construct the SQL to create a view.  Can you post a simple example and expected results?

 

 

Answer 4

Yesterday I walked along the river and saw a guy in the water screaming for help.. "Help!!",  he yelled several times. I asked him why he yelled for help. "I cannot swim", he said, "please help me". I asked him if he had called 911. "I cannot seem to be able to reach my phone he said.. Please help me". Then I asked him if had tried asking other people, but during his answer he drowned.

Strange how more and more people seem to need to react to these forums with statements like 'I do not understand why you need this'. I think that if a person asks for help you either try to help him/her or not.

Anyway I created a nice XSLT, this xslt transforms a XSD (infopath) into a create script for SQL. Now we can insert XMLs in to SQL but do normal queries on the table instead of xpath. Performance is not yet perfect, but it works. Columsn that need to be indexed, are created seperatly as computed column.

If anybody wants this xslt please let me know....

Sander

 

Answer 5

Yes, please post a small sample of you XSD and XSLT.

Answer 6

Unfort. it is not possible to post the xslt here, because of size limits. Please send me an (G)mail at saaffke@ I will send you the xslt.