SQLXML : How to Bulk Load XML From a File Using OPENXML

Using OPENXML you can import XML documents into an XML variable, or into a table that has XML columns. The following is an example:

   1: -- ==========================================================================
   2: -- Object       : OPENXML1.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Developer    : Donabel Santos
   6: -- Origin       : 2008/08/17
   7: -- Last Modified: 2008/10/04
   8: -- Notes        : 
   9: -- ==========================================================================
  10:  
  11: -- bulk load
  12: -- For this example, XML file must be saved in C:
  13: -- XML file also should specify UTF-8 encoding, ie:
  14: -- <?xml version="1.0" encoding="UTF-8"?>
  15:  
  16: DECLARE @XMLTable TABLE
  17: (
  18:    xmlcol XML
  19: )
  20:  
  21: INSERT INTO @XMLTable(xmlcol)
  22: SELECT
  23:     InvoicesXML
  24: FROM
  25: (
  26:     SELECT * 
  27:     FROM OPENROWSET(BULK 'c:invoice.xml',SINGLE_BLOB) AS Invoices
  28: ) AS Invoices(InvoicesXML)
  29:  
  30: SELECT *
  31: FROM @XMLTable
  32:  
  33:  

SQLXML : How to Alter Existing Column Schema (XSD)

Sometimes you may need to change schema definitions in existing columns.

The following shows an example of how you can do this:

   1: -- drop the XML schema from existing column definitions
   2: ALTER TABLE AD
   3:    ALTER COLUMN Title xml
   4:  
   5: -- DROP IF EXISTS
   6: IF    EXISTS (SELECT name 
   7:               FROM  sys.xml_schema_collections 
   8:               WHERE name='TitleSchema')
   9:    DROP  XML SCHEMA COLLECTION TitleSchema
  10: GO
  11:  
  12: -- new definition
  13: -- there can only be one Title element
  14: CREATE XML SCHEMA COLLECTION TitleSchema AS 
  15: '<?xml version="1.0" encoding="utf-8"?>
  16: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  17:   <xsd:element name="Title">
  18:     <xsd:complexType>
  19:          <xsd:attribute name="Type" type="xsd:string" />
  20:          <xsd:attribute name="Value" type="xsd:string" use="optional" />
  21:     </xsd:complexType>
  22:   </xsd:element>
  23: </xsd:schema>'
  24: GO
  25:  
  26: -- IMPORTANT: before you add back the schema to the column 
  27: -- definition, make sure all values in your existing column 
  28: -- comply with the new schema definition
  29: ALTER TABLE AD
  30:    ALTER COLUMN Title xml(TitleSchema)

SQLXML : How to List Columns That Have the XML Data Type

   1: -- this lists the table catalog, table name, 
   2: -- column name, and data type
   3: SELECT 
   4:     TABLE_CATALOG,
   5:     TABLE_NAME,
   6:     COLUMN_NAME, 
   7:     DATA_TYPE 
   8: FROM 
   9:     INFORMATION_SCHEMA.COLUMNS
  10: WHERE 
  11:     DATA_TYPE = 'xml'
  12:  

 

   1: -- this lists the corresponding schemas
   2: SELECT 
   3:     DISTINCT
   4:     OBJECT_NAME(sys.columns.object_id)        AS 'TableName',
   5:     sys.columns.name                    AS 'ColName',
   6:     sys.xml_schema_collections.name            AS 'Schema' 
   7: FROM 
   8:     sys.columns
   9:     LEFT JOIN     sys.xml_schema_collections 
  10:     ON sys.columns.xml_collection_id = sys.xml_schema_collections.xml_collection_id
  11: ORDER BY 
  12:     OBJECT_NAME(sys.columns.object_id), sys.columns.name    
  13:  

SQLXML : How to List Schema Elements and Attributes

   1: SELECT
   2:     sys.xml_schema_collections.xml_collection_id    AS CollectionID,
   3:     sys.xml_schema_collections.name                 AS SchemaName,
   4:     sys.xml_schema_elements.name                    AS ElementName,
   5:     sys.xml_schema_attributes.name                  AS AttributeName
   6: FROM
   7:     sys.xml_schema_collections
   8:     INNER JOIN sys.xml_schema_attributes
   9:     ON sys.xml_schema_collections.xml_collection_id =  sys.xml_schema_attributes.xml_collection_id
  10:     INNER JOIN sys.xml_schema_elements
  11:     ON sys.xml_schema_collections.xml_collection_id = sys.xml_schema_elements.xml_collection_id
  12: WHERE
  13:     sys.xml_schema_collections.name NOT LIKE 'sys'

Sample Result:

SQLXML Sample Result - Elements and Attribute Names

SQLXML : How to Create an XML Schema

The following is an example of how to create an XML Schema in SQL Server.

   1: -- DROP IF EXISTS
   2: IF    EXISTS (SELECT 1 
   3:               FROM  sys.xml_schema_collections 
   4:               WHERE name='SampleSchema')
   5:    DROP XML SCHEMA COLLECTION SampleSchema
   6:  
   7: CREATE XML SCHEMA COLLECTION SampleSchema AS 
   8: '<?xml version="1.0" encoding="utf-8"?>
   9: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  10:   <xsd:element name="Sample">
  11:     <xsd:complexType>
  12:       <xsd:attribute name="SampleID" type="xsd:integer" />
  13:       <xsd:attribute name="Name" type="xsd:string" />
  14:       <xsd:attribute name="Description" type="xsd:string" />
  15:     </xsd:complexType>
  16:   </xsd:element>
  17: </xsd:schema>'

 

To check your XML Schema:

Method 1: Go to your database > Programmability > Types > XML Schema Collections

SQLXML - XML Schema Collection

Method 2: You can use the xml_schema_namespace function to query the schema from within SSMS

   1:  
   2: SELECT
   3:    xml_schema_namespace(N'dbo',N'SampleSchema') 

 

Later on I will post additional sample schemas which use different SQL XML data types.

Generating XSD from an XML File

To create a schema (XSD) from an XML file, you can use a command line tool that comes with Visual Studio called xsd.exe (located in Install DirectoryMicrosoft Visual Studio 8SDKv2.0Bin). If you’re running Visual Studio 2008, your path will be different.

Usage:

xsd.exe -
    Utility to generate schema or class files from given source.
xsd.exe <schema>.xsd /classes|dataset [/e:] [/l:] [/n:] [/o:] [/s] [/uri:]
xsd.exe <assembly>.dll|.exe [/outputdir:] [/type: [...]]
xsd.exe <instance>.xml [/outputdir:]
xsd.exe <schema>.xdr [/outputdir:]

Sample invocation:

C:temp>xsd invoice.xml
Microsoft (R) Xml Schemas/DataTypes support utility
[Microsoft (R) .NET Framework, Version 2.0.50727.42]
Copyright (C) Microsoft Corporation. All rights reserved.
Writing file 'C:tempinvoice.xsd'.
C:temp>

The full options are as follows: Continue reading

Issue: Length of LOB data to be replicated exceeds configured maximum 65536

Error:

Length of LOB data (78862) to be replicated exceeds configured maximum 65536

Scenario:

We published some articles that use varchar(max) and a lot of XML data types for the columns. When we enabled replication, we got the error Length of LOB data (78862) to be replicated exceeds configured maximum 65536

Solution:

Increase the size that can be replicated. This is applicable for transactional replication only.

T-SQL:
EXEC sp_configure ‘max text repl size’, 2147483647

SSMS (excerpt from BOL):

    1. In Object Explorer, right-click a server and select Properties.
    2. Click the Advanced node.
    3. Under Miscellaneous, change the Max Text Replication Size option to the desired value.

Reference:

BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3056cf64-621d-4996-9162-3913f6bc6d5b.htm