Import XML into SQL Server, XML Schema and OPENXML with Example in SQL 2012

The XML Data type, XML values can be stored natively in an xml data type column that can be according to a collection of XML schemas, or left untyped. You can index the XML column.

Capabilities

• Methods for querying data
• Use schemas
• Have indexes
• Primary
• Secondary
1. Value
2. Path
3. Property

Advantages of using XML schemas

• Validate XML
• Choose between a document or document fragment
• Better performance

Now let see how to create sample XML Schema

CREATE XML SCHEMA COLLECTION CategoriesSchema AS

N'<?xml version="1.0"?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="Categories">

<xs:complexType>

<xs:sequence>

<xs:element name="Category" maxOccurs="unbounded">

<xs:complexType>

<xs:sequence>

<xs:element name="Name" type="xs:string" />

</xs:sequence>

<xs:attribute name="ID" type="xs:string" use="required" />

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>'

In this below sample create XML variable using wrong syntax, this will validate by schema and indicate the error message

Once you correct the XML text, command will be success.

OPENXML

Now let see, how can we take the XML data and convert it into tabular format. For this OPENXML is using. There are 2 main useful of OPENXML are

• Shred XML
• Convert XML into tabular data

OPENXML Steps

sp_xml_preparedocument

• Shreds XML
• Parameters
• Document Handle
• XML document

OPENXML

• Parse the document
• Parameters
• Document handle
• Starting element
• Finder option
1. Attributes
2. Elements
3. Both

OPENXML WITH Options

Output name

Data type

Search

• Not required if output name matches path
• Uses XPATH
• @ for an attribute
• / to go down a level
• .. to go up a level

OPENXML is only support untyped XML, so that we can’t define our custom XML Schema

If we wanted to change the name of the attribute or element

DECLARE @Categories xml;

SET  @Categories=

'<Categories>

<Category ID="1">

 <Name>Bikes</Name>

</Category>

<Category ID="2">

 <Name>Clothing</Name>

</Category>

<Category ID="3">

 <Name>Accessories</Name>

</Category>

</Categories>';

DECLARE @documentHandle int;

EXEC sp_xml_preparedocument @documentHandle OUT, @Categories;

SELECT *

FROM OPENXML(@documentHandle,'Categories/Category',3)

WITH (

Name       VARCHAR(50)   'Name',

CategoryID int           '@ID'

);

Cheers!