RE: Why XML Datatype present in SQL Server.

SSC Guru

Points: 61136

May 14, 2013 at 9:13 am

Sergiy (5/8/2013)
Alan.B,
I think you missing the point.
The question was not about use of XML in real life, but about why we cannot store XML files using good old nvarchar/ntext data type.
Your example does not show any use of XML data processing.
Yes, the outcome is an XML file, but you totally operate with varchar values.
You could read, parse, build XML files in SQL Server 2000, having no XML data in place.
Out of curiosity I put your script into Query Analyzer and after minor syntax tweaks it worked fine in SQL 2000:
IF OBJECT_ID('tempdb..#testdata') IS NOT NULL
DROP TABLE #testdata
CREATE TABLE #testdata (id int primary key, txt varchar(100) NOT NULL)
INSERT INTO #testdata
SELECT 1,'Paragraph 1...' UNION ALL
SELECT 2,'Paragraph 2...' UNION ALL
SELECT 3,'Paragraph 3...'
GO
DECLARE @pars AS varchar(1000),
@wordDoc varchar(1000)
SET @pars=''
SET @wordDoc ='
<w:wordDocument xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml">
<w:body></w:body>
</w:wordDocument>'
SELECT @pars=@pars+d+CHAR(13)
FROM (
SELECT '<w:p><w:r><w:t>'+CAST(id AS char(1))+': '+txt+'</w:t></w:r></w:p>'+CHAR(13)
FROM #testdata) data(d)
SELECT REPLACE(REPLACE( @wordDoc,'',
'<?xml version="1.0" encoding="ISO-8859-1"?>'+CHAR(13)+
'<?mso-application progid="Word.Document"?>'),
'',CHAR(13)+@pars)
You see? You do not need XML data type for this at all.

Thanks and sorry for the late reply (been on vacation).

I think that the link you included above pretty much summed it up. I was just adding my 2 cents on XML, the XML datatype, etc. Perhaps I should have included a couple examples.

Below are five real-life examples of where you would use the XML datatype.

/*******************************************************

(0) Create some sample data

Note: For simplicity I am only including XML fragments and

I removed the namespaces, namespace declarations, etc.

*******************************************************/

IF OBJECT_ID('tempdb..#data') IS NOT NULL

DROP TABLE #data;

CREATE TABLE #data (id int primary key, doc xml not null);

DECLARE @worddoc xml='

<wordDocument>

<body>

<r><t>1: Paragraph 1...</t></r>

<r><t>2: Paragraph 2...</t></r>

<r><t>3: Paragraph 3...</t></r>

</body>

</wordDocument>';

DECLARE @ssrs_report xml=

REPLACE(CAST(

'<Report>

<DataSources>

<DataSource Name="QuestNTDev">

<DataSourceReference>Something_Dev</DataSourceReference>

<rd:SecurityType>None</rd:SecurityType>

<rd:DataSourceID>54f84859-75fd-45a8-a5b7-5f6fec3ede7b</rd:DataSourceID>

</DataSource>

</DataSources>

<DataSets>

<DataSet Name="ds_p_major_region"/>

<DataSet Name="ds_p_country_code"/>

<DataSet Name="ds_p_acct_group"/>

<DataSet Name="ds_p_currency_code"/>

<DataSet Name="ds_p_profit_center"/>

<DataSet Name="ds_p_office_id"/>

<DataSet Name="ds_p_total_by_office"/>

<DataSet Name="ds_p_subtotal_on_summary"/>

<DataSet Name="ds_get_spid"/>

<DataSet Name="ds_output"/>

</DataSets>

</Report>' AS varchar(1000)),'rd:','');

DECLARE @events xml='

<Events>

<Event>

<System>

<Provider Name="MSSQL$SQL2012"/>

<EventID Qualifiers="16384">17137</EventID>

<TimeCreated SystemTime="2012-05-10T16:18:53.000000000Z"/>

<EventRecordID>16563</EventRecordID>

<Channel>Application</Channel>

<Computer>SQLSERVER1</Computer>

</System>

<EventData>

<Data>ReportServer$SQL2012TESTTempDB</Data>

<Binary>F14200000A000000...</Binary>

</EventData>

</Event>

<Event>

<System>

<Provider Name="MSSQL$SQL2012"/>

<EventID Qualifiers="19999">1000225</EventID>

<TimeCreated SystemTime="2013-05-6T16:18:53.000000000Z"/>

<EventRecordID>16999</EventRecordID>

<Channel>Application</Channel>

<Computer>SQLSERVER1</Computer>

</System>

<EventData>

<Data>blah</Data>

<Binary>F14200000A000000...</Binary>

</EventData>

</Event>

</Events>';

DECLARE @ssis_package xml=

REPLACE(CAST(

'<DTS:Executable DTS:ExecutableType="SSIS.Package.2">

<DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property>

<DTS:Property DTS:Name="VersionComments"></DTS:Property>

<DTS:Property DTS:Name="CreatorName">DOMAIN\username</DTS:Property>

<DTS:Property DTS:Name="CreatorComputerName">AJBTEST</DTS:Property>

<DTS:Property DTS:Name="CreationDate"

DTS:DataType="7">11/25/2012 3:35:28 PM</DTS:Property>

</DTS:Executable>

' AS varchar(1000)),'DTS:','');

DECLARE @sqlTrace xml='

<TraceData>

<Events>

<Event id="65534" name="Trace Start">

<Column id="14" name="StartTime">2013-05-10T17:21:28.007-05:00</Column>

</Event>

<Event id="13" name="SQL:BatchStarting">

<Column id="1" name="TextData">SELECT 1</Column>

<Column id="3" name="DatabaseID">7</Column>

<Column id="35" name="DatabaseName">ajbTest</Column>

<Column id="12" name="SPID">60</Column>

<Column id="14" name="StartTime">2013-05-10T17:21:48.917-05:00</Column>

<Column id="26" name="ServerName">MYSERVER\SQL2012Express</Column>

</Event>

<Event id="65533" name="Trace Stop">

<Column id="14" name="StartTime">2013-05-10T17:21:54.46-05:00</Column>

</Event>

</Events>

</TraceData>'

INSERT INTO #data

SELECT 1, @worddoc UNION ALL SELECT 2, @ssrs_report UNION ALL

SELECT 3, @events UNION ALL SELECT 4, @ssis_package UNION ALL SELECT 5, @sqlTrace;

/*******************************************************

(1) Reading from a word doc

*******************************************************/

SELECT p.value('(r/t/text())[1]', 'varchar(20)') AS p_num

FROM #data

CROSS APPLY doc.nodes('wordDocument/body/p') AS doc(p)

WHERE id=1;

/*******************************************************

(2) Reading from an SSRS Report

*******************************************************/

SELECTdatasource.value('(DataSourceReference/text())[1]', 'varchar(20)') AS DataSource,

dataset.value('(@Name)', 'varchar(20)') AS DataSet

FROM #data

CROSS APPLY doc.nodes('Report/DataSources/DataSource') AS datasources(datasource)

CROSS APPLY doc.nodes('Report/DataSets/DataSet') AS datasets(dataset)

WHERE id=2;

/*******************************************************

(3) Reading from extracted event viewer data

*******************************************************/

SELECT[System].value('(Computer/text())[1]', 'varchar(20)') AS SQLBox,

[System].value('(../EventData/Data/text())[1]', 'varchar(50)') AS DB,

[System].value('(TimeCreated/@SystemTime)[1]', 'varchar(20)') AS StartupTime

FROM #data

CROSS APPLY doc.nodes('Events/Event/System') AS events([System])

WHERE id=3

AND [System].value('(EventRecordID/text())[1]', 'int')=16563

AND [System].value('(Channel/text())[1]', 'varchar(20)')='Application';

/*******************************************************

(4) Reading from a SSIS package

*******************************************************/

SELECTx.value('(@ExecutableType)[1]', 'varchar(100)') AS PkgType,

x.value('(Property[@Name="CreatorName"])[1]', 'varchar(100)') AS Creator,

x.value('(Property[@Name="CreationDate"])[1]', 'varchar(100)') AS CreatedOn,

x.value('(Property[@Name="CreatorComputerName"])[1]', 'varchar(100)') AS Computer

FROM #data

CROSS APPLY doc.nodes('Executable') AS ex(x)

WHERE id=4;

/*******************************************************

(5) Reading from an SQL trace (XML format)

*******************************************************/

SELECT e.value('(@name)[1]', 'varchar(100)') AS [event],

e.value('(Column[@name="StartTime"]/text())[1]', 'varchar(100)') AS StartTime,

e.value('(Column[@id="1"]/text())[1]', 'varchar(100)') AS Command

FROM #data

CROSS APPLY doc.nodes('TraceData/Events/Event') AS events(e)

WHERE id=5;

--Cleanup

DROP TABLE #data;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001