Showing posts with label XMLSCHEMA. Show all posts
Showing posts with label XMLSCHEMA. Show all posts

SQL Server XML Bulk load examples

This article is about bulk loading XML file into tables (shredding XML data into columns of table)  :
i) Loading single table
ii)Loading multiple tables (With Foreign key relationship)
iii)Handling identity column insert while loading XML file
iv)Handling table creation before loading XML file

Prerequisites :  (Install SQLXML 4.0 SP1)

As mentioned in MSDN , after SQL Server 2008, the latest version of SQLXML (SQLXML 4.0 SP1) is no longer included in SQL Server. To install SQLXML 4.0 SP1 when it is available, download it from  -

Step by step Installation guide for SQLXML 4.0 SP1 - 

i) Example : Loading single table

Step 1 : Create table

USE [AdventureWorks2012]

CREATE TABLE Student (Id INT , Name VARCHAR(100))

Step 2 : Open notepad and place the XML and save it as Studxml.xml


SQL Server - XML Examples(Xpath,Xquery,XML DML,XML Index,XML Schema)

Example 1:

Mostly we need  XML in this format,i.e column names with their values enclosed within their column name tag

Consider below XML needs to be generated for the below mentioned sample table definition

<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>
<field Name="ID">2</field>
<field Name="Name">Sunny</field>
<field Name="Age">24</field>
<field Name="Sex">Female</field>

Name VARCHAR(100),
Age INT,
Sex VARCHAR(50))

INSERT @Employee SELECT 1,'Sathya',25,'Male'
INSERT @Employee SELECT 2,'Sunny',24,'Female'

DECLARE @xmldata XML

SET @xmldata = (SELECT ID,Name,Age,Sex FROM @Employee FOR XML PATH (''))

SET @xmldata = (
SELECT ColumnName AS "@Name",
       ColumnValue AS "text()"
SELECT i.value('local-name(.)','varchar(100)') ColumnName,
       i.value('.','varchar(100)') ColumnValue
FROM @xmldata.nodes('//*[text()]') x(i)) tmp
FOR XML PATH ('field'),root('Employee'))

SELECT @xmldata

Example 2:

If XML is stored as NTEXT and if we are required to make changes in the node values of that XML

Sample XML,

<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>

All about XML in SQL Server

SQL Server handles two types of data

1.)Relational data
2.)XML data

I have covered almost all topics about handling XML in SQL Server and posted in my site.


      How to convert relational data to XML data ?

      answer :       use FOR XML clause

For more information about FOR XML clause, refer below link


How to traverse through XML document/fragment to fetch/modify attribute values or element nodes?

answer use XQUERY & XPATH

 How to shred XML data into columns of relational tables? 

 answer : use nodes() &  OPENXML

 How to validate structure & values of XML,each time when it is inserted/updated?


 How to improve the performance of querying XML data ?

  answer : use XML INDEX - primary & secondary index.

For more information about XQUERY,XPATH,XMLINDEX,XMLSCHEMA clause, refer below link



Ø  XQuery is the language for querying XML data.

   Ø  XPath was designed to navigate an XML document to retrieve the documents elements and attributes.

     Ø  XQuery is built on XPath expressions.

Ø To put it simple XQuery,XPath are used to traverse through XML document/fragment to fetch/modify attribute values or element nodes.

XQuery FLWOR Expression:

  • for - (optional) binds a variable to each item returned by the in expression
  • let - (optional) to assign value to variable
  • where - (optional) specifies a criteria
  • order by - (optional) specifies the sort-order of the result
  • return - specifies what to return in the result.

Ø  The xml data type features several built-in methods that allow you to manipulate XML instance data. 

These methods allow you to query, modify, or shred your XML data into relational form.

query()   -The query() method allows you to perform an XQuery on your xml instance.
The result returned is untyped XML. 

Syntax: DbObject.query('XQuery')

value()   - The value() method allows you to perform an XQuery on your xml instance and returns a scalar value cast to a SQL Server data type.

Syntax: DbObject.value('XQuery', 'SqlType')

exist() - The exist() method allows you to specify an XQuery on your xml instance and returns a SQL bit value of 1 if the XQuery returns a result, 0 if the XQuery returns no result, or NULL if the xml instance is NULL.

Syntax :DbObject.exist('XQuery')

modify()                 - The modify() method allows you to execute XML Data Manipulation Language (XML DML) statements against an xml instance. The modify() method can only  be used with a SET clause or statement.

Syntax :DbObject.modify('XQuery')

nodes() - The nodes() method allows you to shred xml instances. Shredding is the process of converting your XML data to relational form.

Syntax :DbObject.nodes('XQuery') AS TableAlias(ColumnAlias)

XML DML Keywords:

Delete - Deletes the node specified by an XQuery path expression.

Insert -  Inserts one or more nodes as the children or siblings of a node specified
by an XQuery path expression.

replace value of    -  Updates the value of a node specified by an XQuery path.

--Examples for XQUERY & XPATH

--lets us  create sample data