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
IE 7
Firefox 2.0
