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"

uv · 5 Nov 2010 at 12:30

what client are you using, can you use PLSQL, and what datatype is the xml column?

*that reg plate is rediculously close to mine, only one letter out :/

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

uv · 5 Nov 2010 at 13:07

I'm afraid I haven't used sql2005 (I use Orcale SQL with SQL+ / PLSQL), but the below query would work with my setup, hopefully it works with yours too

Code:

select    substr(
            xml,
            (instr(xml,'Object Name="')+13),
            instr(substr(xml,instr(xml,'Object Name="')+13),'"')-1) as obj_name,
        to_date(replace(substr(
            xml,
            (instr(xml,'TimeStamp="')+11),19
            ),'T',' '),'YYYY-MM-DD HH24:MI:SS') as time_stamp
from    videoos_transact.GenericXmlInfo
where id = yourIDhere;

no idea why my first query was so convoluted :/

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.

s-p · 5 Nov 2010 at 14:07

I've only used the XML datatype once, so I'm sure there are far better solutions.

Try something like...

Code:

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

For the date field, it depends on the consistency of the value held in the XML data column. If it was me, I'd probably just use the REPLACE function to remove unwanted characters and then cast as a date where required.

You'll have to check the syntax of the above, as I've not got SQL Management Studio installed here at home to test.

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]

uv · 5 Nov 2010 at 14:12

A[L]C;17723141 said:
unfortunately not. I get

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

Ugh, I couldn't live without instr

ah well, hope you get it sorted

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

thanks anyway

s-p · 5 Nov 2010 at 15:07

A[L]C;17723187 said:
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]

Sorry, my fault. The value query needs to contain the namespace if one is specified in the XML data.

Try this...

Code:

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

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>

s-p · 5 Nov 2010 at 15:48

It should be case of simply changing the path + datatype in the previous code.

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]', 'datetime')
  FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]

As you're performing more than one query, you may want to review using WITH XMLNAMESPACES to reduce the amount of declarations you're performing.

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

I'm getting

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

s-p · 5 Nov 2010 at 16:06

A[L]C;17724160 said:
I'm getting

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

Put a comma at the end of line 2. I really am rubbish at writing sql without Management Studio.

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?

uv · 5 Nov 2010 at 16:44

A[L]C;17724475 said:
The only issue I have, is how would I specify a time period using the where clause?

Just do a to_date with a replace for the 'T'.

to_date(replace(substr(the_time_varchar,1,19),'T',' '),'YYYY-MM-DD HH24:MI:SS')

then your time is in DATE format.

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.

s-p · 5 Nov 2010 at 17:08

A[L]C;17724687 said:
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.

Try...

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', ''))
  FROM [VideoOS_Transact].[dbo].[GenericXmlInfo]

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?