Exploring XML in SQL Server 2005
Host: Govind Kanshi, Architect Evangelist, Microsoft
September 10, 2004
Anoop_ms : Welcome to MSDN Chat, Today's topic is Exploring XML in SQL Server 2005
Govind_Host : Good afternoon everybody, thanks for attending chat on XML. I hope everybody knows about XML and used it and is here to find out what and how in terms of its integration with SQL Server 2005(the next version of Sql Server).
Govind_Host : Can we take a quick poll and find out who uses xml today with SQL Server 2000 or any other database.
Anoop_ms We have with the Expert Host Govind Kanshi, Architect Evangelist, Microsoft INdia
Govind_Host : XML Datatype integration into SQL Server allows us to store XML as doc or fragment as native datatype just like varchar
Govind_Host : or int etc. The maximum size limit on one instance is about 2 GB. We support different methods on the XML datatype (query/value/exist/modify/nodes etc).
Govind_Host : The presence of this datatype allows us to use it as parameter to stored proc, col in table etc.
Govind_Host : Eg...var/par decalaration - declare @xdoc xml
Govind_Host : Col Declaration - create table T1 (…, x xml)
Govind_Host : You can read from a file the xml information- CAST(SELECT * FROM OPENROWSET (BULK somefile.xml', SINGLE_BLOB) as xml)
Govind_Host : Since XML has become the native datatype so the casting is also allowed -
Govind_Host : set @xdoc = CAST(N'…' as xml)
Govind_Host : The XML type can be restrained by XML Schema so that you can control the shape of the data which you can enter inside.
Govind_Host : To make sure you get that certain kind of data can enter the column we can create a schema which is shred down to native relations inside sql system tables.
Govind_Host : CREATE XML SCHEMA COLLECTION S1 AS '')
Moderator_MSDNIndia : (Kiran) we are using SQL server for one of our products and Oracle for anohter. Ino oracle, we are able to retrieve the data in the xml format directly. Is this possible with SQL Server 2005 now?
Govind_Host : One can always retrieve the data in XML format for long time even 2000. Please look up FOR XML syntaz
Govind_Host : XSL can be applied at the client side, we don't support XSL application right at the retrieval. Although one can achieve it by writing CLR stored procedure now.
Govind_Host : Kiran...Did that answer that question?
Govind_Host : Please look up - http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsql90/html/sql25xmlbp.asp
Govind_Host : it has the CLR func to achieve the same
Govind_Host : as a sample
Govind_Host : This link is the whitepaper for the best practices to be followed for usage of XML inside SQL 2005
Moderator_MSDNIndia : (kiran) Can we use these XML Geenrating REports using REporting Services?
Govind_Host : Reporting Services can be customized to use the data type, yes.
Govind_Host : The XML document inside SQL 2005 should always have (ideally) schema associated with it. It helps us to layout data in better way and queries more efficient.
Govind_Host : This datatype is directed towards content management/unstructured information storage/exchange of information with another party.
Govind_Host : We also support the XQuery on this datatype which ofcourse is a working draft
Moderator_MSDNIndia : (kiran)So, in simple words, I can use CLr to generate XML as per XSL and use the same in Reporting services and the XML should have a schema associated with it. Isn't it?
Govind_Host : yeah..Kiran that is right, since this column can store xml information, it is always better to provide shape information and constrain legal information to be entered
Govind_Host : There few good SQL Team bloggers on XML
Govind_Host : We have article in the MSDN magazine at - http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5xml.asp
Govind_Host : on XML,
Govind_Host : Vinod has written some cool articles -
Govind_Host : Accessing FOR XML Output in SQL Server:
Govind_Host : Finding XML system objects in SQL Server 2005:
Govind_Host : Exploring XML in Yukon(SQL Server 2005):
Govind_Host : So let us get back to XML Datatype....So now we know it is like any other datatype, if it needs the schema we provide it.
Moderator_MSDNIndia : (Anjana) Can u tell me what's the max size of XML data type?
Govind_Host : Anjana - 2 GB is the max size of the instance which you can store inside XML datatype.
Govind_Host : We have introduced an XML data modification language that allows users to insert subtrees, delete subtrees and update scalar values, to specify points of insertion (before, after, into) and XQuery to locate the node for insertion/de
Govind_Host : For folks worrying about the versioning of the data ...
Govind_Host : XML schema collection supports XML schema evolution. Users can add both the old and the new XML schemas to the same XML schema collection. This allows XML instances conforming to the different versions of the XML schemas to be stored in the same XML
Govind_Host : column. Users can query for instances conforming to each of these XML schemas separately as well as query across them both. Users can add more schemas to the XML schema collection as XML schemas evolve further.
Moderator_MSDNIndia : (Anjana) So, when the data size grows with the size of the XMl text inserted (like varchar), or it's always 2GB?
Govind_Host : Anjana - the maximum size which can be stored inside it is 2 GB, so the instance does not take up 2 GB just because you start using the xml datatype
Govind_Host : so your hunch is right.
Govind_Host : Have anyone of you installed SQL Server 2005 - you can try the express edition from the website today
Moderator_MSDNIndia : (Kiran)I saw some thing called as "Annotated XML Schema " can u explain it in breif please "
Govind_Host : Anjana - for XML type is internally a special type of varchar(max), you don't give the size for it.
Govind_Host Annotations to the XDR schema allow you to map different elements to the fields in the database and assign your own element name attributes. Using annotations to the XDR schema is an alternative to using the XML FOR EXPLICIT mode, which is quite complex.
Govind_Host : Kiran - Visit - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexxml/html/xml07162001.asp , end of the day it is simply xml-to-relational mapping
Govind_Host : Once you build the mapping of the xml data to relational world, then it becomes a view to constrain the information entered
Govind_Host : The XML datatype can be indexed to provide faster access to the interesting information. XML is very interesting. The information
Govind_Host : you are interested could be inside a node at particular depth or in the attribute at depth etc. The XML is by nature hierarchical and order preserving.
Govind_Host : What we have done is to create a real flexible indexing mechanism called ORDPATH (paper published in SIGMOD)
Govind_Host : This creates index on path, tags and value (b+tree). Our schema allows insertion of subtrees anywhere within an XML tree,
Govind_Host : CREATE PRIMARY XML INDEX idx_1 ON docs (xDoc) ,
Govind_Host : The indices is used when your search specifies tags or values or both to speeds up queries. A query can include accesses to both relational and XML columns. The entire query is optimized based upon algorithms used inside the query optimizer.
Govind_Host : The XML filter has been added to the Full Text Engine thus allowing us to mix the two systems in our queries
Govind_Host : Most importantly the advice is to think about the fact when to use this datatype. Because how is all part of the manuals
Govind_Host : Yes Kiran that is the Paper at http://portal.acm.org/citation.cfm?id=1007686
Govind_Host : Thanks Kiran.
Govind_Host : Here we have a table Product Docs, with one xml column.
Govind_Host : Surely Kiran , please use the datatype(sql2005) with reporting services and if you face any problems let us know
Moderator_MSDNIndia : (kiran_tbg) Do you suggest using this for Reporting services? We have requirement for reporting services and I am toying with the idea of using XML for them
Govind_Host : Now we will load the data from a file ...INSERT INTO Product Docs(productdoc) SELECT * FROM OPENROWSET ( BULK 'G:\xml\xmlin.xml', SINGLE_BLOB) AS TEMP GO
Govind_Host : Simple query could be SELECT xDoc.query ('/city/country') FROM XMLTable GO
Govind_Host : To just get data ....as dataset .SELECT xDoc.query ('data(/city/country)') FROM XMLTable GO
Govind_Host : Update XMLTable SET xDoc.modify('insert Here is the inserted Record after (/city/country/continent)[1]') Where id = 1
Govind_Host : The FOR XML feature is supported and extended through the SQL server 2005.
Govind_Host : http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/default.aspx?pull=/library/en-us/dnsql90/html/forxml2k5.asp has more idea
Govind_Host : http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5xml.asp is another whitepaper
Govind_Host : Are there any questions ...
Govind_Host : This datatype is very powerful when constrained by schema and indexed. This information can be queried wrt to relational data.
Govind_Host : So SQL 2005 supports the XML view where one may want to maintain the relational data
Govind_Host : and another option is to use directly the data type which is the new feature of the SQL server 2005
Govind_Host : There are ways to extend this type by using SQLCLR inside the database, we have also upgraded the client side apis to support the new type.
Moderator_MSDNIndia : (Kiran_tbg) Is there any place through which we can contact you in case we get some issues?
Govind_Host : You can always send question to govindk@microsoft.com
Govind_Host : but better place in "world is falling down emergency" is to visit the newsgroups or local user groups where there more brains
Govind_Host : to help out.
Govind_Host : Sure....Anytime for the database related questions - govindk and ramkoth (UDM guy)
Govind_Host : Please try the software from earlier mentioned site
Govind_Host : And provide the feedback on the usage
Govind_Host : Pick up the - http://www.daveandal.net/books/8391/ for getting updated on system.xml changes
Moderator_MSDNIndia : Any more questions for the expert we are at the end of the MSDN Chat Session
Govind_Host : And read ...http://www.25hoursaday.com/weblog/
Moderator_MSDNIndia : Thank you all for participating in today's MSDN Chat
Govind_Host : Thanks again for the time you spent. Please use the software and forward any questions/feedback.
Moderator_MSDNIndia : Next Week's MSDN Chat Topic is "ClickOnce: Curing Deployment Headaches " and the expert handling your questions will be Gaurav Khanna
