What is Typed vs. Untyped XML in SQL Server ? Explain with an example

 Posted by Pandians on 8/12/2009 | Category: Sql Server Interview questions | Views: 13288
Answer:

Untyped XML :
I have created one Table named : Table1

CREATE TABLE TABLE2

(XMLSample XML)
GO
INSERT TABLE2 VALUES('TEST')
INSERT TABLE2 VALUES('123')

- The table should not allow any TYPE of data other than XML format with specific element. But it allows. This is called 'UnTyped XML '.

Typed XML :
I create one Table with one column as XML datatype. It should allow only INTEGER type of data along with some specific XML Element. This is called 'Typed XML '

1. I have to define one XML SCHEMA ( Type of the XML Element and Name of the Element, Etc., )
CREATE XML SCHEMA COLLECTION PandianXMLSchema AS '

<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Dotnetfunda" type="int"/>
</schema>'
GO

I have created one XML SCHEMA named 'PandianXMLSchema ' ( It will say what is the name of the 'Element ' - and Type of the Element ).

2. I create one Table with one column as XML datatype along with XML SCHEMA which we created above. It should allow only INTEGER type of data along with Dotnetfunda XML Element alone.
CREATE TABLE TABLE2

(XMLSample XML(PandianXMLSchema))
GO

3. Inserting data into the TABLE2
INSERT TABLE2 VALUES('TEST')

- It will throw an Err
Msg 6909, Level 16, State 1, Line 1
XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content. Location: /

Because, the data 'TEST' is not an INTEGER and Its not encloused with 'Dotnetfunda ' element.
INSERT TABLE2 VALUES('123')

- It will throw an Err
Msg 6909, Level 16, State 1, Line 1
XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content. Location: /

Because, the data '123' is an INTEGER. But Its not encloused with 'Dotnetfunda ' element.
INSERT TABLE2 VALUES('<Dotnetfunda>TEST</Dotnetfunda>')

- It will also throw an Err
Msg 6926, Level 16, State 1, Line 1

XML Validation: Invalid simple type value: 'TEST'. Location: /*:Dotnetfunda[1]

The data 'TEST' encloused with proper element, But Its not an INTEGER type.
INSERT TABLE2 VALUES('<Dotnetfunda>123</Dotnetfunda>')

(1 row(s) affected)

XMLSample
<Dotnetfunda>123</Dotnetfunda>

Cheers


Asked In: In General | Alert Moderator 

Comments or Responses

Login to post response