SQL Database with XML content

[s-p]

Thats excellent s-p.

The date is coming out as 2010-11-04 06:37:57.270

Is there any way I can get it to be 2010-11-04 06:37:57 ie knock off the last 4 digits? No problem if not, I can do it in excel.

Also how would I search on timestamp?

Sorry, back at work this week so only just seen your post.

Knocking off the last 4 digits is a presentation issue and therefore something I'd recommend doing in Excel in all honesty. That being said, we can obtain the ODBC Canonical date format that you desire with a slight modification to the SQL.

SELECT 
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Object/@Name)[1]','nvarchar(max)'),
CONVERT(VARCHAR(19), CONVERT(DateTime, REPLACE(GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/@TimeStamp)[1]', 'nvarchar(max)'), '+00:00', '')), 120)
FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]

As for searching on the timestamp, it depends what type of search you're planning on doing? i.e. Do you need the ability to return all records where the date+time is before/after a certain point, or are you solely interested in querying the time aspect of the field.

 

[A[L]C]

Yeah Id like to be able to return the reg and timestamp where the timestamp is between two values

 

[s-p]

Yeah Id like to be able to return the reg and timestamp where the timestamp is between two values

Here's a sample:

SELECT Name, CONVERT(VARCHAR(19), TimeStamp, 120)
FROM (
SELECT 
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Object/@Name)[1]','nvarchar(max)') As Name,
CONVERT(DateTime, REPLACE(GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/@TimeStamp)[1]', 'nvarchar(max)'), '+00:00', '')) As TimeStamp
FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]) As Table1
WHERE CONVERT(VARCHAR, TimeStamp, 108) BETWEEN '09:00:00' AND '21:00:00'

Just replace the two time parameters in the WHERE clause with the boundaries you need to restrict by.

 

[A[L]C]

Hey dude,

How would I get the device name from

<Device GUID="B6017215-42A3-4111-8B5E-0B517BAA043E" Name="Camera 1">

though

SELECT
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Object/@Name)[1]','nvarchar(max)'),
CONVERT(DateTime, REPLACE(GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/@TimeStamp)[1]', 'nvarchar(max)'), '+00:00', '')),
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Device/@Name)[1]','nvarchar(max)')
FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]

would work but it didnt


edit: got it

SELECT
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Object/@Name)[1]','nvarchar(max)'),
CONVERT(DateTime, REPLACE(GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/@TimeStamp)[1]', 'nvarchar(max)'), '+00:00', '')),
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Source/C:Device/@Name)[1]','nvarchar(max)')
FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]

Edit:

How do I give them column names? So I can order by?

Ahhh like this

SELECT
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Object/@Name)[1]','nvarchar(max)') as Registration,
CONVERT(DateTime, REPLACE(GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/@TimeStamp)[1]', 'nvarchar(max)'), '+00:00', '')) as Timestamp,
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Source/C:Device/@Name)[1]','nvarchar(max)') as Camera
FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]

edit:

Hmmm this doesnt work?

SELECT
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Object/@Name)[1]','nvarchar(max)') as Registration,
CONVERT(DateTime, REPLACE(GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/@TimeStamp)[1]', 'nvarchar(max)'), '+00:00', '')) as Timestamp,
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Source/C:Device/@Name)[1]','nvarchar(max)') as Camera
FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]
WHERE CONVERT(VARCHAR, TimeStamp, 108) BETWEEN '2010-11-15 00:00:00' AND '2010-11-15 16:57:00'

Msg 207, Level 16, State 1, Line 6
Invalid column name 'TimeStamp'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'TimeStamp'.