Simple xml processing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple xml processing

Author

Topic

dariopalermo
Starting Member

16 Posts

Posted - 2011-06-09 : 06:37:41

Hello guys, here's my question:

Got a table with some fields... among them, there's one like this:
<HED>2060</HED><SMX>1</SMX><HDP>10</HDP><ALT>36</ALT><SAT>7</SAT>

datatype is varchar(500)

I cannot modify the source table.

I've got to make a trigger on insert on that table so that some fields are copied 1on1 and the xml-like field is splitted in 5 different fields.

Just to make an example, source table is like:

Vehicle,Lat,Lon,xml_like_data

and dest table is like

Vehicle,Lat,Lon,Heading,Speed,HDOP,Altitude,Satellites

(as you can guess, the xml_data field contains gps additional values).

The trigger now works with a complex combination of substring, charindex and len (and additional isnumber an len for integer value checking) functions to extract values from the xml_data as a string. I'm wondering: casting the field to xml datatype could make it simpler to extract the values from the string?

Bye,

Dario

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-09 : 07:39:13

You can use one of the xml functions such as value or query on the XML column. The syntax would be something like this:


INSERT INTO yourNewTable
SELECT
   i.vehicle,
   i.Lat,
   I.Lon,
   c.value('(//Speed)[1]','int'),
   c.value('(//HDOP)[1]','varchar(32)'),
   c.value('(//Altitude)[1]','float'),
   c.value('(//Satellites)[1]','int')
FROM
   INSERTED i
   CROSS APPLY  i.xmlcol.nodes('.') T(c)

dariopalermo
Starting Member

16 Posts

Posted - 2011-06-10 : 05:03:49

thank you sunitabeck, you put me on the right track. I'm having an hard time, though, because the source column is varchar and not xml (it's content IS xml, the datatype isn't). Is there a way to cast it right away to xml?

Obviously, "... CROSS APPLY cast(i.xmlcol as xml).nodes('.') T(c)" doesn't work.

I would prefer not to change the source table...

Bye,

Dario

dariopalermo
Starting Member

16 Posts

Posted - 2011-06-10 : 05:49:13

Never mind, changed the source table... ;)

Thanks

Dario

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-10 : 08:47:28

This probably is only of academic interest to you now, but you can always cast the varchar column as XML in one virtual table and then use that virtual table to do the shredding. For example, like this:

FROM
   INSERTED i
   CROSS APPLY (SELECT CAST(i.varcharCol AS XML) AS xmlcol) ix
   CROSS APPLY  ix.xmlcol.nodes('.') T(c)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources