Thursday, September 08, 2011 7:55 AM
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
Thursday, September 08, 2011 8:58 AM
Friday, September 09, 2011 12:20 PMModerator
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
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 email@example.com.
- Proposed As Answer by Stephanie LvModerator Thursday, September 15, 2011 6:29 AM
Saturday, September 10, 2011 12:49 PMAnswerer
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?
Wednesday, September 28, 2011 7:20 AM
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....
Wednesday, September 28, 2011 10:06 AMAnswererYes, please post a small sample of you XSD and XSLT.
Friday, October 21, 2011 8:52 AMUnfort. 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.