April 24, 2021 at 7:16 am
For column with Xml data type "Is xml Document" property present under column property. What does Is xml Document means?
I got the below definition from this link
Once you've decided on a typed or untyped XML datatype field, you must determine whether you will be storing a XML document based on a single Schema, multiple Schemas, or fragments of XML.
Selecting yes for the "Is XML Document" option in the datatype configuration dialog sets the field to accept a single XML document associated with a single Schema.
Setting "Is XML Document" to no configures the field for all other options, including fragments of XML data.
I have attached xml and the schema files. Can anyone explain what is use of that column property and how to use it.
April 24, 2021 at 8:34 am
DOCUMENT means that the xml must be a valid XML document and not just a fragment. That is, there must be one single root node around the whole thing. The script below illustrates. The xml has two instances of the <bookstore> node, which is supposed to be the top node. This is accepted for the column x1, which does not have the DOCUMENT constraint, but it is not accepted for x2.
CREATE XML SCHEMA COLLECTION
myxmlschema1 AS
N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="bookstore">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="book">
<xs:complexType>
<xs:sequence>
<xs:element name="title">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="lang" type="xs:string" use="required" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element maxOccurs="unbounded" name="author" type="xs:string" />
<xs:element name="year" type="xs:unsignedShort" />
<xs:element name="price" type="xs:decimal" />
</xs:sequence>
<xs:attribute name="category" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';
DROP TABLE IF EXISTS #temp
go
CREATE TABLE #temp (x1 xml (myxmlschema1) NULL,
x2 xml(DOCUMENT myxmlschema1) NULL)
go
-- Succeeds
INSERT #temp (x1) VALUES ('<bookstore>
<book category="drama">
<title lang="en">To Kill a Mockingbird</title>
<author>Harper Lee</author>
<year>1953</year>
<price>18.2</price>
</book>
</bookstore>
<bookstore>
<book category="science fiction">
<title lang="en">Foundation</title>
<author>Isaac Asimov</author>
<year>1941</year>
<price>22</price>
</book>
</bookstore>')
go
-- Fails
INSERT #temp (x2) VALUES ('<bookstore>
<book category="drama">
<title lang="en">To Kill a Mockingbird</title>
<author>Harper Lee</author>
<year>1953</year>
<price>18.2</price>
</book>
</bookstore>
<bookstore>
<book category="science fiction">
<title lang="en">Foundation</title>
<author>Isaac Asimov</author>
<year>1941</year>
<price>22</price>
</book>
</bookstore>')
go
-- Succeeds
INSERT #temp (x2) VALUES ('<bookstore>
<book category="drama">
<title lang="en">To Kill a Mockingbird</title>
<author>Harper Lee</author>
<year>1953</year>
<price>18.2</price>
</book>
</bookstore>'),
('<bookstore>
<book category="science fiction">
<title lang="en">Foundation</title>
<author>Isaac Asimov</author>
<year>1941</year>
<price>22</price>
</book>
</bookstore>')
go
SELECT * FROM #temp
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply