SSMS2012: Doing Non-DTD XML Data in RDBMS - Can the Relationships be dealt by using mapping between RDBMS & Non-DTD XML Data?
Category: sql server xml
Scott H Chang on Thu, 13 Aug 2015 14:55:38
I have learned the Microsoft XML SCHEMA COLLECTIONs for non-DTD XML data in Microsoft SQL Server 2012 Management Studio 2012 (SSMS2012) for several months. From Chapter 1: XML Design for Data of the book "Professional XML Databases" written by Kevin Williams, et. al. (published by Wrox, 2000), I see it discusses (1) Relationships, (2) More Complex Relationships - Pointers , (3) More Complex Relationships - Containment for the DTD XML Data related to the RDBMS. I wonder whether the Relationships can be dealt by using mapping between RDBMS and Non-DTD XML Data!? If it can be dealt, how can it be done? Please kindly help and enlighten me in understanding this matter.
Thanks in advance,
P. S. To: Mr. Eric Zhang, Hi Eric, you helped me so much in doing the Microsoft XML SCHEMA COLLECTIONS in SSMS2012 and DECLARE xml data into the Tables of my SSMS2012 Databases in the last several months. Is the xml data (I have used in the last several months) validated by the Microsoft XML SHEMA COLLECTIONs the DOM-type data?
Yesterday, I executed the following code in my SSMS2012:
--shcXSD7LaxValidate.sql ////Programming Microsoft SQL Server 2012 Listings 6-7 on Pages 263-264 -- saved in C:\Documents\Microsoft Press\ProgrammingMicrosoftSQLServer2012\C06Code-XML folder -- 13 May 2015 13:30 PM / 18 May 2015 08:22 AM corrected -- Final Trial: 12 August 2015 PM -- USE AdventureWorks; -- USE SampleDB; Use EricZhangDB; GO --DROP XML SCHEMA COLLECTION OrdersXSD -- Modified Listing 6-4 Creating an XML Schema Definition (XSD) (on Pages 260, 263 & 264) CREATE XML SCHEMA COLLECTION OrdersXSD AS ' <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schema-microsoft-com:mapping-schema"> <xsd:simpleType name="OrderAmountFloat" > <xsd:restriction base="xsd:float" > <xsd:minExclusive value="1.0" /> <xsd:maxInclusive value="5000.0" /> </xsd:restriction> </xsd:simpleType> <xsd:element name="Orders"> <xsd:complexType> <xsd:sequence> <xsd:element name="Order"> <xsd:complexType> <xsd:sequence> <xsd:element name="OrderID" type="xsd:int" /> <xsd:element name="CustomerID" type="xsd:int" /> <xsd:element name="OrderDate" type="xsd:dateTime" /> <xsd:element name="OrderAmount" type="OrderAmountFloat" /> <xsd:any namespace="##other" processContents="lax"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>' GO -- Listing 6-5 Creating a new table with an xml column to an XML Schema Definition (XSD) -- must have this table created first, after the XSD created/completed///18 May 2015 08:45 Am IF EXISTS(SELECT name FROM sys.tables WHERE name = 'OrdersXML' AND type = 'U') DROP TABLE OrdersXML CREATE TABLE OrdersXML( OrdersID int PRIMARY KEY, OrdersDoc xml(OrdersXSD) NOT NULL) --Listing 6-7 Using lax schema validation with XML data -- This should work because all XSD validation succeed INSERT INTO OrdersXML VALUES(6, ' <Orders> <Order> <OrderID>6</OrderID> <CustomerID>60</CustomerID> <OrderDate>2011-10-10T14:22:27.25-05:00</OrderDate> <OrderAmount>25.90</OrderAmount> <Notes xmlns="sf">My notes for this order</Notes> </Order> </Orders>') GO
It worked nicely. I saw the dboOrdersXML and the dbo.OrdersXSD in the Tables and the Programmability=>Types=>XML XML Schema Collections of EricZhangDB of my SSMS2012. Furthermore, I saw the second Column 'OrderDoc' with the xml data "<Orders><Order><OrderID>5</OrderID>.....</Orders>" in the dbo.OrdersXML. I am pleased and surprised to see the xml data that appeared in the second Column 'OrderDoc'!! If I want to do the XQuery on xml data in the second Column 'OrderDoc' of dbo.OrdersXML of EricZhangDB in my SSMS2012, how can I do it? Please kindly help and tell me how I can handle this matter. Please respond.
Many Thanks again, Scott Chang
Eric__Zhang on Wed, 19 Aug 2015 09:10:31
Is the xml data (I have used in the last several months) validated by the Microsoft XML SHEMA COLLECTIONs the DOM-type data?
I don't have much knowledge on DOM-type, so you may have to learn it from here.
If I want to do the XQuery on xml data in the second Column 'OrderDoc' of dbo.OrdersXML of EricZhangDB in my SSMS2012, how can I do it?
Please see a simple demostration against the column OrdersDoc.
--get the OrderID SELECT T.n.value('OrderID','VARCHAR(10)') OrderID FROM OrdersXML o CROSS APPLY o.OrdersDoc.nodes('/Orders/Order') AS T(n)
TechNet Community Support