Following is the example of how to create schema, where it is store schema information which we've created,
and how to bind schema with xml column and sample stores proc that is would consume typed xml.
PS: When the XML data is associated with an XML schema collection, it is called typed XML; otherwise it is called untyped XML.
-- Step [1] -----------------------------------------------------------------------------------
CREATE XML SCHEMA COLLECTION DestinationSchemaCollection AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="CustomerRS">
<xs:complexType>
<xs:sequence>
<xs:element ref="Destinations"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Destinations">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" ref="Destination"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Destination">
<xs:complexType>
<xs:sequence>
<xs:element ref="Type" maxOccurs="1" minOccurs="0"/>
<xs:element ref="Code" maxOccurs="1" minOccurs="1"/>
<xs:element ref="Description" maxOccurs="1" minOccurs="1"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Type" type="xs:integer"/>
<xs:element name="Code" type="xs:integer"/>
<xs:element name="Description" type="xs:string"/>
</xs:schema>
'
-- Step [2] -----------------------------------------------------------------------------------
SELECT *
FROM sys.xml_schema_collections
WHERE name = 'DestinationSchemaCollection'
-- Step [3] -----------------------------------------------------------------------------------
CREATE TABLE dbo.Destinations
(
ID INT IDENTITY (1,1),
DestinationDescription XML ( DestinationSchemaCollection ) NOT NULL --Assign schema to xml column
)
-- Step [4] -----------------------------------------------------------------------------------
CREATE PROCEDURE dbo.InsertDestination
(
@DestinationDescription XML
)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION InsertDestinations
INSERT INTO dbo.Destinations ( DestinationDescription )
SELECT @DestinationDescription
COMMIT TRANSACTION InsertDestinations
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION InsertDestinations
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
SELECT
@ErrorNumber = Error_NUMBER(),
@ErrorSeverity = Error_SEVERITY(),
@ErrorState = Error_STATE(),
@ErrorLine = Error_LINE(),
@ErrorProcedure = Error_PROCEDURE(),
@ErrorMessage = Error_MESSAGE();
RAISError
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine
);
END CATCH
END
-- Step [5] -----------------------------------------------------------------------------------
/* Below are the samples to execute above sp */
DECLARE @DestinationXML XML
SET @DestinationXML = '
<CustomerRS>
<Destinations>
<Destination>
<Type>3</Type>
<Code>97</Code>
<Description>Europe</Description>
</Destination>
<Destination>
<Type>4</Type>
<Code>98</Code>
<Description>Australia</Description>
</Destination>
</Destinations>
</CustomerRS>'
EXEC [InsertDestination] @DestinationXML
-- try [5.1] -----------------------------------------------------------------------------------
DECLARE @DestinationXML XML
SET @DestinationXML = '
<CustomerRS>
<Destinations>
<Destination>
<Code>97</Code>
<Description>Paris</Description>
</Destination>
<Destination>
<Type>4</Type>
<Code>98</Code>
<Description>India</Description>
</Destination>
</Destinations>
</CustomerRS>'
EXEC [InsertDestination] @DestinationXML
-- try [5.2] -----------------------------------------------------------------------------------
DECLARE @DestinationXML XML
SET @DestinationXML = '
<CustomerRS>
<Destinations>
<Destination>
<Type>Sample</Type>
<Code>97</Code>
<Description>USA</Description>
</Destination>
<Destination>
<Type>4</Type>
<Code>98</Code>
<Description>SA</Description>
</Destination>
</Destinations>
</CustomerRS>'
EXEC [InsertDestination] @DestinationXML
-- try [5.3] -----------------------------------------------------------------------------------
DECLARE @DestinationXML XML
SET @DestinationXML = '
<CustomerRS>
<Destinations>
<Destination>
<Type>3</Type>
<Description>Madrid</Description>
</Destination>
<Destination>
<Type>4</Type>
<Code>98</Code>
<Description>UK</Description>
</Destination>
</Destinations>
</CustomerRS>'
EXEC [InsertDestination] @DestinationXML
No comments:
Post a Comment