Parsing XML Data

• Azzu

  Ten Centuries

  Points: 1363

  More actions

  July 8, 2010 at 1:24 am

  #387298

  Hi

  I have the following XML string

  '<SampleXML>

  <Colors>

  <Color1>White</Color1>

  <Color2>Blue</Color2>

  <Color3>Black</Color3>

  <Color4 Special="Light">Green</Color4>

  <Color5>Red</Color5>

  </Colors>

  <Fruits>

  <Fruits1>Apple</Fruits1>

  <Fruits2>Pineapple</Fruits2>

  <Fruits3>Grapes</Fruits3>

  <Fruits4>Melon</Fruits4>

  </Fruits>

  </SampleXML>'

  my question is how to knoiw whether the string is a valid xml or not. Preferably i'd like to do it using TSql.

  Thanks in advance

  Azeem

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  July 8, 2010 at 2:10 am

  #1190045

  Try this blog[/url]

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 8, 2010 at 4:15 pm

  #1190542

  Azzu (7/8/2010)

  ---

  Hi

  I have the following XML string

  '<SampleXML>

  <Colors>

  <Color1>White</Color1>

  <Color2>Blue</Color2>

  <Color3>Black</Color3>

  <Color4 Special="Light">Green</Color4>

  <Color5>Red</Color5>

  </Colors>

  <Fruits>

  <Fruits1>Apple</Fruits1>

  <Fruits2>Pineapple</Fruits2>

  <Fruits3>Grapes</Fruits3>

  <Fruits4>Melon</Fruits4>

  </Fruits>

  </SampleXML>'

  my question is how to knoiw whether the string is a valid xml or not. Preferably i'd like to do it using TSql.

  Thanks in advance

  Azeem

  The link skcadavre provided can be used to verify if a valid xml document is also valid if validation rules against the content are applied.

  If you'd just need to know if a string is a valid xml document in the first place (e.g. each open tag is followed by a close tag a.s.o.) you could simply use a TRY CATCH block and convert the string to an XML data type. As long as the CATCH block isn't called, the xml is considered to be valid as per the SQL Server definition of a valid XML document.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]