Date with T in XML

Category: sql server xml

Question

Madhubala18 on Sat, 04 May 2013 07:05:23


Hi all,

when i form xml with for xml option,i get dates with T in between

<reportinfo reportId="1" reportdate="2013-05-04T11:21:21.137" />
<reportinfo reportId="2" reportdate="2013-05-04T11:21:21.137" />
<reportinfo reportId="3" reportdate="2013-05-04T11:21:21.137" />

how to avoid T?

Replies

SathyanarrayananS on Sat, 04 May 2013 07:47:31


Hi,

Try like this,

DECLARE @tmp TABLE (ReportId INT,Reportdate DATETIME)
INSERT @tmp SELECT 1,'2013-05-04 11:21:21.137'
INSERT @tmp SELECT 2,'2013-05-04 11:21:21.137'
INSERT @tmp SELECT 3,'2013-05-04 11:21:21.137'
SELECT ReportId,
CONVERT(CHAR(10),Reportdate,127)+CHAR(32)+CONVERT(CHAR(12),Reportdate,114) Reportdate 
FROM @tmp Reportinfo FOR XML AUTO

wBob on Tue, 07 May 2013 15:54:44


Hi,

the reason SQL Server adds the 'T' is because this is a valid XML dateTime.  The valid formats are listed here:

http://www.w3schools.com/schema/schema_dtypes_date.asp

If you attempted to associate your date with an XML Schema Collection and use the xs:dateTime datatype, then it will fail, eg

USE tempdb
GO

CREATE XML SCHEMA COLLECTION xsc_test AS
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<xs:element name="root">
		<xs:complexType>
			<xs:sequence>
				<xs:element maxOccurs="unbounded" name="reportinfo">
					<xs:complexType>
						<xs:attribute name="reportId" type="xs:unsignedByte" use="required" />
						<xs:attribute name="reportdate" type="xs:dateTime" use="required" />
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>'
GO


DECLARE @xml XML(xsc_test) = '<root>
<reportinfo reportId="1" reportdate="2013-05-04T11:21:21.137" />
  <reportinfo reportId="2" reportdate="2013-05-04T11:21:21.137" />
  <reportinfo reportId="3" reportdate="2013-05-04T11:21:21.137" />
</root>'

--select @xml

SELECT ri.c.value('@reportdate', 'datetime')
FROM @xml.nodes('root/reportinfo') ri(c)
GO


-- Try and associate this string date with the same schema collection
DECLARE @xml XML(xsc_test) = '<root>
<reportinfo reportId="1" reportdate="2013-05-04 11:21:21.137" />
  <reportinfo reportId="2" reportdate="2013-05-04 11:21:21.137" />
  <reportinfo reportId="3" reportdate="2013-05-04 11:21:21.137" />
</root>'

--select @xml

SELECT ri.c.value('@reportdate', 'datetime')
FROM @xml.nodes('root/reportinfo') ri(c)
GO

DROP XML SCHEMA COLLECTION xsc_test
GO

So your code is equivalent to storing a date in a varchar column in a SQL Server table.  You wouldn't do that in SQL, why would you do it when using XML?