SQL Server Typed XML using XSD Part 3


Below is a bit of code from stackoverflow.com that was posted here.

I modified the code from stackoverflow.com by first dropping the table before dropping the schema collection. I also added a SELECT statement at the end.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xmlTestTable]') 
AND TYPE IN (N'U'))
DROP TABLE [dbo].[xmlTestTable]
GO
-- You need to drop the table before the schema because otherwise you will get this error:
-- Msg 6328, Level 16, State 1, Line 9
-- Specified collection 'test' cannot be dropped because it is used by object 'dbo.xmlTestTable'.
IF EXISTS (SELECT * FROM sys.xml_schema_collections
WHERE [name] = 'test')
DROP XML SCHEMA COLLECTION test
GO
DECLARE @testSchema XML;
SET @testSchema = N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://view2/DHCantata_Local/test.xsd" elementFormDefault="qualified" attributeFormDefault="unqualified">
 <xs:element name="testXML">
  <xs:annotation>
   <xs:documentation>Just a test schema</xs:documentation>
  </xs:annotation>
  <xs:complexType>
   <xs:sequence>
    <xs:element name="element1" minOccurs="0" maxOccurs="unbounded"/>
    <xs:element name="element2" minOccurs="0" maxOccurs="unbounded"/>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>';
CREATE XML SCHEMA COLLECTION test AS @testSchema
GO
CREATE TABLE [dbo].[xmlTestTable](
 [textColumn] [nvarchar](max) NULL,
 [XMLColumn] [XML](CONTENT [dbo].[test]) NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.xmlTestTable
VALUES ('some text','<testXML xmlns = "http://view2/DHCantata_Local/test.xsd">
<element1>Some text 1</element1>
<element2>Some Text 2</element2>
</testXML>')
GO
SELECT [textColumn],[XMLColumn] FROM [XML].[dbo].[xmlTestTable]
GO

I tried this code in SQL Server and it works fine.

Leave a comment

Your email address will not be published. Required fields are marked *