SQL Database with XML content

A[L]C · 5 Nov 2010 at 11:06

Hi there,

I have a database (videoos_transact) with a table (GenericXmlInfo) which contains a column called 'Xml'.

This contains information such as

Code:

<Alert xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/Alert.xsd" ID="0" TimeStamp="2010-11-04T06:37:57.27+00:00" Name="Plate AY03AAU" Type="0">
  <Source>
    <Server GUID="2d68a369-2af3-4ad4-b098-92732d70137e" Name="http://localhost">
      <Description>LPR Server</Description>
    </Server>
    <Device GUID="B6017215-42A3-4111-8B5E-0B517BAA043E" Name="Camera 1">
      <Location Name="In Barrier" />
    </Device>
  </Source>
  <Rule Name="Unregistered Plates" Type="Unregistered Plates" />
  <Object Name="AY03AAU" Type="License Plate" Confidence="999">
    <Polygon>
      <Point X="617" Y="84" />
      <Point X="773" Y="74" />
      <Point X="774" Y="103" />
      <Point X="618" Y="112" />
      <Color R="255" G="0" B="0" A="255" />
    </Polygon>
  </Object>
  <Description>License Plate Detected. Country: GB. Confidence: 999.</Description>
  <Vendor Name="Dacolian" />
</Alert>

How would I construct a query only to pull out the 'Object Name' ie AY03AAU and the TimeStamp ie "2010-11-04T06:37:57.27+00:00" (but shown as "2010-11-04 6:37:57"

A[L]C · 5 Nov 2010 at 12:41

the datatype is xml

at the mo im just using the sql management console

the database is sql 2005

A[L]C · 5 Nov 2010 at 14:05

unfortunately not. I get

Msg 195, Level 15, State 10, Line 3
'instr' is not a recognized built-in function name.

A[L]C · 5 Nov 2010 at 14:12

Thanks, I received results, but it was just Null values

SELECT
GenericXmlInfo.Xml.value('(/Alert/Object/@Name)[1]','nvarchar(max)')
FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]

A[L]C · 5 Nov 2010 at 14:14

thanks anyway

A[L]C · 5 Nov 2010 at 15:25

Legend!

Just need to get the timestamp now....

Some more examples

Code:

<Alert xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/Alert.xsd" ID="0" TimeStamp="2010-11-04T06:37:57.27+00:00" Name="Plate AY03AAU" Type="0">
  <Source>
    <Server GUID="2d68a369-2af3-4ad4-b098-92732d70137e" Name="http://localhost">
      <Description>LPR Server</Description>
    </Server>
    <Device GUID="B6017215-42A3-4111-8B5E-0B517BAA043E" Name="Camera 1">
      <Location Name="In Barrier" />
    </Device>
  </Source>
  <Rule Name="Unregistered Plates" Type="Unregistered Plates" />
  <Object Name="AY03AAU" Type="License Plate" Confidence="999">
    <Polygon>
      <Point X="617" Y="84" />
      <Point X="773" Y="74" />
      <Point X="774" Y="103" />
      <Point X="618" Y="112" />
      <Color R="255" G="0" B="0" A="255" />
    </Polygon>
  </Object>
  <Description>License Plate Detected. Country: GB. Confidence: 999.</Description>
  <Vendor Name="Dacolian" />
</Alert>

Code:

<Alert xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/Alert.xsd" ID="0" TimeStamp="2010-11-04T06:40:12.544+00:00" Name="Plate AY03AAL" Type="0">
  <Source>
    <Server GUID="2d68a369-2af3-4ad4-b098-92732d70137e" Name="http://localhost">
      <Description>LPR Server</Description>
    </Server>
    <Device GUID="B6017215-42A3-4111-8B5E-0B517BAA043E" Name="Camera 1">
      <Location Name="In Barrier" />
    </Device>
  </Source>
  <Rule Name="Unregistered Plates" Type="Unregistered Plates" />
  <Object Name="AY03AAL" Type="License Plate" Confidence="996">
    <Polygon>
      <Point X="819" Y="329" />
      <Point X="1026" Y="316" />
      <Point X="1026" Y="356" />
      <Point X="819" Y="368" />
      <Color R="255" G="0" B="0" A="255" />
    </Polygon>
  </Object>
  <Description>License Plate Detected. Country: GB. Confidence: 996.</Description>
  <Vendor Name="Dacolian" />
</Alert>

Code:

<Alert xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/Alert.xsd" ID="0" TimeStamp="2010-11-04T06:45:55.679+00:00" Name="Plate AY03AAL" Type="0">
  <Source>
    <Server GUID="2d68a369-2af3-4ad4-b098-92732d70137e" Name="http://localhost">
      <Description>LPR Server</Description>
    </Server>
    <Device GUID="B6017215-42A3-4111-8B5E-0B517BAA043E" Name="Camera 1">
      <Location Name="In Barrier" />
    </Device>
  </Source>
  <Rule Name="Unregistered Plates" Type="Unregistered Plates" />
  <Object Name="AY03AAL" Type="License Plate" Confidence="995">
    <Polygon>
      <Point X="818" Y="330" />
      <Point X="1026" Y="317" />
      <Point X="1026" Y="356" />
      <Point X="818" Y="367" />
      <Color R="255" G="0" B="0" A="255" />
    </Polygon>
  </Object>
  <Description>License Plate Detected. Country: GB. Confidence: 995.</Description>
  <Vendor Name="Dacolian" />
</Alert>

Code:

<Alert xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/Alert.xsd" ID="0" TimeStamp="2010-11-04T06:51:53.276+00:00" Name="Plate AY03AAL" Type="0">
  <Source>
    <Server GUID="2d68a369-2af3-4ad4-b098-92732d70137e" Name="http://localhost">
      <Description>LPR Server</Description>
    </Server>
    <Device GUID="B6017215-42A3-4111-8B5E-0B517BAA043E" Name="Camera 1">
      <Location Name="In Barrier" />
    </Device>
  </Source>
  <Rule Name="Unregistered Plates" Type="Unregistered Plates" />
  <Object Name="AY03AAL" Type="License Plate" Confidence="999">
    <Polygon>
      <Point X="817" Y="330" />
      <Point X="1025" Y="318" />
      <Point X="1026" Y="356" />
      <Point X="817" Y="368" />
      <Color R="255" G="0" B="0" A="255" />
    </Polygon>
  </Object>
  <Description>License Plate Detected. Country: GB. Confidence: 999.</Description>
  <Vendor Name="Dacolian" />
</Alert>

A[L]C · 5 Nov 2010 at 15:58

I'm getting

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

A[L]C · 5 Nov 2010 at 16:28

You're pretty darn good in my opinion!

I received

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'datetime'.

So I changed it to

Code:

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

Im now getting

Code:

2010-11-04T06:40:12.544+00:00
2010-11-04T06:45:55.679+00:00
2010-11-04T06:51:53.276+00:00
2010-11-04T07:02:21.141+00:00
2010-11-04T07:05:55.766+00:00
2010-11-04T07:06:26.637+00:00
2010-11-04T07:06:26.898+00:00
2010-11-04T07:07:03.14+00:00
2010-11-04T07:07:14.409+00:00
2010-11-04T07:13:09.019+00:00
2010-11-04T07:15:01.269+00:00

which is a lot further than I was before! Thank you very much.

I guess I would just use excel text to columns to split the data/time down. Its always in the same format.

The only issue I have, is how would I specify a time period using the where clause?

A[L]C · 5 Nov 2010 at 16:54

That went over my head lol sorry

I wondered if you meant this

Code:

SELECT 
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/C:Object/@Name)[1]','nvarchar(max)'),
GenericXmlInfo.Xml.value('declare namespace C="http://tempuri.org/Alert.xsd"; (/C:Alert/@TimeStamp)[1]', to_date(replace(substr(the_time_varchar,1,19),'T', ' '),'YYYY-MM-DD HH24:MI:SS'))
  FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]

but I got

Msg 195, Level 15, State 10, Line 3
'substr' is not a recognized built-in function name.

A[L]C · 5 Nov 2010 at 18:24

thanks will try it monday

A[L]C · 8 Nov 2010 at 09:18

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?

A[L]C · 8 Nov 2010 at 19:22

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

A[L]C · 15 Nov 2010 at 15:24

Hey dude,

How would I get the device name from

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

though

Code:

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

Code:

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

Code:

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?

Code:

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'.