Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jun 26th, 2012  |  syntax: None  |  size: 2.15 KB  |  hits: 8  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. MS SQL Server: What is the best way to determine if a given string is a valid XML or not?
  2. declare @T table(ID int, Col1 nvarchar(1000))
  3.  
  4. insert into @T values
  5. (1, 'No xml value 1'),
  6. (2, 'No xml value 2'),
  7. (3, '<root><item>Text value in xml</item></root>')
  8.  
  9. select
  10.   case when charindex('<item>', Col1) = 0
  11.   then Col1
  12.   else
  13.     substring(Col1, charindex('<item>', Col1)+6, charindex('</item>', Col1)-charindex('<item>', Col1)-6)
  14.   end  
  15. from @T
  16.        
  17. No xml value 1
  18. No xml value 2
  19. Text value in xml
  20.        
  21. DECLARE @table TABLE (myXML XML)
  22.  
  23. INSERT INTO @table
  24. SELECT  
  25. '
  26.     <Employee>
  27.         <FirstName>Henry</FirstName>
  28.         <LastName>Ford</LastName>
  29.     </Employee>
  30. '
  31.  
  32. SELECT myXML
  33. FROM @table
  34. FOR XML RAW
  35.        
  36. DECLARE @table TABLE (myXML XML)
  37.  
  38. INSERT INTO @table
  39. SELECT  
  40. '
  41.     <Employee
  42.         <FirstName>Henry</FirstName>
  43.         <LastName>Ford</LastName>
  44.     </Employee>
  45. '
  46.  
  47. SELECT myXML
  48. FROM @table
  49. FOR XML RAW
  50.        
  51. BEGIN TRY
  52.     DECLARE @myXML XML
  53.     SET @myXML = CAST
  54.     ('
  55.         <Employee>
  56.             <FirstName>Henry</FirstName>
  57.             <LastName>Ford</LastName>
  58.         </Employee>
  59.     ' AS XML)
  60.     SELECT 'VALID XML'
  61. END TRY
  62. BEGIN CATCH
  63.     SELECT 'INVALID XML'
  64. END CATCH;
  65.        
  66. BEGIN TRY
  67.     DECLARE @myXML XML
  68.     SET @myXML = CAST
  69.     ('
  70.         <Employee
  71.             <FirstName>Henry</FirstName>
  72.             <LastName>Ford</LastName>
  73.         </Employee>
  74.     ' AS XML)
  75.     SELECT 'VALID XML'
  76. END TRY
  77. BEGIN CATCH
  78.     SELECT 'INVALID XML'
  79. END CATCH;
  80.        
  81. CREATE XML SCHEMA COLLECTION UserSchemaCollection AS
  82. N'<?xml version="1.0" encoding="UTF-16"?>
  83.   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  84.     <xsd:element name = "User" >
  85.         <xsd:complexType>
  86.             <xsd:sequence>
  87.                 <xsd:element name = "UserID" />
  88.                 <xsd:element name = "UserName" />
  89.             </xsd:sequence>
  90.         </xsd:complexType>
  91.     </xsd:element>
  92.   </xsd:schema>';
  93.  
  94.  
  95. DECLARE @x XML(UserSchemaCollection)
  96. SELECT @x = '<User><UserID>1234</UserID><UserName>Sebastian</UserName></User>'
  97.        
  98. DECLARE @y XML(UserSchemaCollection)
  99. SELECT @y = '<User><UserName>Sebastian</UserName></User>'
  100.        
  101. DECLARE @z XML(UserSchemaCollection)
  102. SELECT @z = 'Some text'
create a new version of this paste RAW Paste Data