Getting data from within an XML tree stored in a blob

• Greg Knaddison

  SSC Eights!

  Points: 859

  More actions

  July 21, 2005 at 4:55 pm

  #76380

  Hello,

  I have a some data stored in XML in a field in a table. I didn't make, it, but I get to report off of it

  So, I'm hoping that someone here has some brilliant ideas on getting data out of an xml tree in a field. Something like:

  select t.field.xml_path

  from t

  Thanks kindly.

  Greg

• Calvin Lawson

  SSChampion

  Points: 11030

  More actions

  July 22, 2005 at 2:22 pm

  #576566

  If you are using SQL2K, you should definitely not use the database to parse your XML. The simple answer is to use an XML parser. You can use .NET or myriad other options.

  String manipulation in the database is relatively primitive. The example below does something rather simple, but look how complex the code is (even when breaking up steps for clarity). And this won't handle multiple nodes correctly, it will just return you the 1st.

  -----------------------------------------------------------------

  create function UTIL_TAGParser(@Tag varchar(255), @String varchar(8000))

  returns varchar(8000)

  as

  BEGIN

  declare @TagLen int,

  @ValueLen int,

  @Startint,

  @subLenint,

  @ENDTagvarchar(255)

  if @String is null goto fEND

  ---CleanUp String

  select @tag = replace(@Tag, '', ''),

  @tag = replace(@Tag, '/', ''),

  @ENDTag = '',

  @tag = ''

  select@TagLen = len(@Tag),

  @ValueLen = len(@String),

  @Start = charindex(@Tag, @String) + @TagLen,

  @subLen = charindex(@ENDTag, @String) - @Start,

  @String= substring(@String, @Start, @subLen)

  fEND:

  return @String

  END

  Signature is NULL

• Greg Knaddison

  SSC Eights!

  Points: 859

  More actions

  July 22, 2005 at 2:29 pm

  #576569

  Hi,

  Thanks for the advice. I'm doing cursor processing (this is only on maybe 10,000 records per month and run once a month) and using OpenXML to get at the data. It's not pretty, but it works!

  Thanks again,

  Greg