Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 2, 2007

Query XML data from a table with XML data type

By Muthusamy Anantha Kumar aka The MAK

The objective of this article is to guide Microsoft SQL Server Database Administrators on how to:

  • Create XML Schema
  • Create a table with XML data type
  • Import XML file to the table with XML data type
  • Query the XML file
  • Query the XML file and produce results, similar to results produced by Transact SQL Statements

Step1

Let us create a C:\XML\Customer1.XML File as shown below. This XML file contains data related to one customer.

<?xml version="1.0" standalone="yes"?>
<Customer>
  <CustomerLogInfo>
    <Date>2007-03-31T06:40:38.0000000-05:00</Date>
    <user>james.brewer</user>
    <Userid>1AE</Userid>
    <ls>A-Accessible</ls>
    <eqtid>761</eqtid>
    <es>Stopped</es>
    <tp>30</tp>
  </CustomerLogInfo>
  <CustomerLogInfo>
    <Date>2007-03-31T06:40:38.0000000-05:00</Date>
    <user>james.brewer</user>
    <Userid>1AE</Userid>
    <ls>Not-Accessible</ls>
    <eqtid>870</eqtid>
    <es>Stopped</es>
    <tp>30</tp>
  </CustomerLogInfo>
  <CustomerLogInfo>
    <Date>2007-03-31T06:40:38.0000000-05:00</Date>
    <user>james.brewer</user>
    <Userid>1AE</Userid>
    <ls>A-Accessible</ls>
    <eqtid>97F</eqtid>
    <es>Started</es>
    <tp>30</tp>
  </CustomerLogInfo>
</Customer>

Step 2

Let us create a database and create an XML Schema collection as shown below.

USE [master]
GO
/****** Object:  Database [XMLTest]    Script Date: 04/17/2007 01:49:43 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'XMLTest')
DROP DATABASE [XMLTest]
go
create database XMLTest
go
use XMLTest
go
Create XML Schema Collection XMLTrack
as
N'<xs:schema
attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Customer">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="CustomerLogInfo">
<xs:complexType>
<xs:sequence>
<xs:element name="Date" type="xs:string" />
<xs:element name="user" type="xs:string" />
<xs:element name="Userid" type="xs:string" />
<xs:element name="ls" type="xs:string" />
<xs:element name="eqtid" type="xs:string" />
<xs:element name="es" type="xs:string" />
<xs:element name="tp" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
go

Note: Update the Schema collection based on your data in your XML file.

Step 3

Let us create a table with XML data type:

USE [XMLTest]
GO
/****** Object:  Table [dbo].[XMLFiles]    Script Date: 04/17/2007 02:07:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XMLFiles]') AND type in (N'U'))
DROP TABLE [dbo].[XMLFiles]
 
create table XMLFiles(Fileid int identity(1,1),
ImportedDate datetime constraint xmldatestamp default getdate(),
Filename varchar(500),
data xml (XMLTrack))

Step 4

Let us import the XML file we created, C:\XML\Customer1.XML, using the openrowset function as shown below.

USE [XMLTest]
go
INSERT INTO XMLFiles(Filename,DATA)
 SELECT 'Customer1' a,* 
FROM OPENROWSET( BULK 'C:\XML\Customer1.xml' ,SINGLE_CLOB)
as mytable
go

Note: The SINGLE_BLOB keyword imports the entire XML file to the XML data type column.

Step 5

Let us query the XMLFiles table using the transact SQL below.

USE [XMLTest]
go
select * from XMLFiles where FileId=1
go

This statement would produce the following results: [refer Fig 1.0].


Fig 1.0

When you click on the data, it also shows the XML data. [refer fig 1.1]

<Customer>
  <CustomerLogInfo>
    <Date>2007-03-31T06:40:38.0000000-05:00</Date>
    <user>james.brewer</user>
    <Userid>1AE</Userid>
    <ls>A-Accessible</ls>
    <eqtid>761</eqtid>
    <es>Stopped</es>
    <tp>30</tp>
  </CustomerLogInfo>
  <CustomerLogInfo>
    <Date>2007-03-31T06:40:38.0000000-05:00</Date>
    <user>james.brewer</user>
    <Userid>1AE</Userid>
    <ls>Not-Accessible</ls>
    <eqtid>870</eqtid>
    <es>Stopped</es>
    <tp>30</tp>
  </CustomerLogInfo>
  <CustomerLogInfo>
    <Date>2007-03-31T06:40:38.0000000-05:00</Date>
    <user>james.brewer</user>
    <Userid>1AE</Userid>
    <ls>A-Accessible</ls>
    <eqtid>97F</eqtid>
    <es>Started</es>
    <tp>30</tp>
  </CustomerLogInfo>
</Customer>


Fig 1.1

Step 6

Now let us query the XML data from the table to produce a transact SQL like result set. Execute the below XQuery as shown below.

SELECT 
ref.value ('Date', 'nvarchar(364)') as [Date],
ref.value ('user', 'nvarchar(364)') as [User],
ref.value ('Userid', 'nvarchar(364)') as [Userid],
ref.value ('ls', 'nvarchar(364)') as [ls],
ref.value ('eqtid', 'nvarchar(364)') as [eqtid],
ref.value ('es', 'nvarchar(364)') as [es],
ref.value ('tp', 'nvarchar(364)') as [tp]
FROM   XMLFiles CROSS APPLY Data.nodes ('//Customer/CustomerLogInfo') R(ref)
where Fileid=1

This would produce the results shown below [refer Fg 1.2]

Date,User,Userid,ls,eqtid,es,tp
2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,A-Accessible,761,Stopped,30
2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,Not-Accessible,870,Stopped,30
2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,A-Accessible,97F,Started,30


Fig 1.2

Step 7

Now let us repeat step 4 and import the data again.

USE [XMLTest]
go
INSERT INTO XMLFiles(Filename,DATA)
 SELECT 'Customer1' a,* 
FROM OPENROWSET( BULK 'C:\XML\Customer1.xml' ,SINGLE_CLOB)
as mytable
go

Step 8

Now let us query the table as shown below.

USE [XMLTest]
go
select * from XMLFiles
go

This would produce the following result.


Fig 1.3

Step 9

In order to show all the data from both rows, we could write the query shown below.

SELECT 
ref.value ('Date', 'nvarchar(364)') as [Date],
ref.value ('user', 'nvarchar(364)') as [User],
ref.value ('Userid', 'nvarchar(364)') as [Userid],
ref.value ('ls', 'nvarchar(364)') as [ls],
ref.value ('eqtid', 'nvarchar(364)') as [eqtid],
ref.value ('es', 'nvarchar(364)') as [es],
ref.value ('tp', 'nvarchar(364)') as [tp]
FROM   XMLFiles CROSS APPLY Data.nodes ('//Customer/CustomerLogInfo') R(ref)
where Fileid=1
union all
SELECT 
ref.value ('Date', 'nvarchar(364)') as [Date],
ref.value ('user', 'nvarchar(364)') as [User],
ref.value ('Userid', 'nvarchar(364)') as [Userid],
ref.value ('ls', 'nvarchar(364)') as [ls],
ref.value ('eqtid', 'nvarchar(364)') as [eqtid],
ref.value ('es', 'nvarchar(364)') as [es],
ref.value ('tp', 'nvarchar(364)') as [tp]
FROM   XMLFiles CROSS APPLY Data.nodes ('//Customer/CustomerLogInfo') R(ref)
where Fileid=2

This would produce the following result. Refer fig 1.4

Date,User,Userid,ls,eqtid,es,tp
2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,A-Accessible,761,Stopped,30
2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,Not-Accessible,870,Stopped,30
2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,A-Accessible,97F,Started,30
2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,A-Accessible,761,Stopped,30
2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,Not-Accessible,870,Stopped,30
2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,A-Accessible,97F,Started,30

Note: If you are planning to display all of the data from each row from the XML data type, you could create a stored procedure with temp table or cursor etc.


Fig 1.4

Conclusion

This article has illustrated how to create an XML Schema, create a table with XML data type, import the XML file to the table with XML data type, query the XML file and Query the XML file and produce results similar to results produced by the Transact SQL Statements result set.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM