Sql-Server
SQL Server Try_Cast 返回 Null 值
,TRY_CAST(Question_Text AS xml).value('(/p/text())[1]','nvarchar(1000)') AS Question_Text2在 CharlieFace 在我之前的文章中的幫助下，我發現了在 SQL Server 中使用 Try_Cast 函式。我仍然遇到 Try_Cast 返回一些NULL值的問題。Question_Text 中的某些數據似乎不適用於 XHTML，我該如何糾正這個問題。
/****** Object: Table [dbo].[tblQuestionsSample] Script Date: 12/10/2021 5:12:16 PM ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblQuestionsSample]') AND type in (N'U')) DROP TABLE [dbo].[tblQuestionsSample] GO /****** Object: Table [dbo].[tblQuestionsSample] Script Date: 12/10/2021 5:12:16 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblQuestionsSample]( [QuestionsRecID] [int] IDENTITY(1,1) NOT NULL, [Question_ID] [int] NULL, [Question_Text] [varchar](1000) NULL, [Question_Text2] [varchar](1000) NULL CONSTRAINT [PK_dbo.[QuestionsRecID] PRIMARY KEY CLUSTERED ( [QuestionsRecID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [dbo].[tblQuestionsSample] ([Question_ID] ,[Question_Text] VALUES (6571,'<p><span style="color: rgb(29, 88, 196); font-family: Raleway, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;">Capstan Hoist EXPIRATION DATE</span></p>') ,(6554,'<p>Do you have a current 'Hazard Communications' certification to submit for verification?</p>') ,(6505,'<p>92) SC - Utilities are Identified/Obtained Valid Locate Tickets/Are Guarded? </p>') ,(6569,'<p><span style="color: rgb(29, 88, 196); font-family: Raleway, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;">Authorized Climber and Rescue EXPIRATION DATE</span></p>') ,(6531,'<p>9) Action (Who, What, When?)</p>') ,(6570,'<p><span style="color: rgb(29, 88, 196); font-family: Raleway, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;">Blood-Borne Pathogens EXPIRATION DATE</span></p>') goQuestion_Text2 欄位應用於更正後的 Question_Text 數據。
Question_Text2 中的數據應該是，請參閱下面的範例更正：
可以提供更多數據樣本。
| Question_id | question_text2 should read| | ------------- | -------------------------------------------------------------------------- | | 6571 | Capstan Hoist EXPIRATION DATE | | 6554 | Do you have the current Hazard certification to submit for verification? | | 6505 | SC - Utilities are Identified/Obtained Valid Locate Tickets/Are Guarded? | | 6569 | Authorized Climber and Rescue EXPIRATION DATE | | 6531 | Action (Who, What, When? | | 6570 | Blood-Borne Pathogens EXPIRATION DATE | | ------------- | -------------------------------------------------------------------------- |非常感謝任何幫助。
謝謝，凱倫
12/20/2021:
DECLARE @dtd nvarchar(max) = N' <!DOCTYPE xhtml [ <!ENTITY nbsp " "> <!-- no-break space = non-breaking space, U+00A0 ISOnum --> <!ENTITY iexcl "¡"> <!-- inverted exclamation mark, U+00A1 ISOnum --> <!ENTITY cent "¢"> <!-- cent sign, U+00A2 ISOnum --> <!ENTITY pound "£"> <!-- pound sign, U+00A3 ISOnum --> <!ENTITY curren "¤"> <!-- currency sign, U+00A4 ISOnum --> <!ENTITY yen "¥"> <!-- yen sign = yuan sign, U+00A5 ISOnum --> <!ENTITY brvbar "¦"> <!-- broken bar = broken vertical bar, U+00A6 ISOnum --> <!ENTITY sect "§"> <!-- section sign, U+00A7 ISOnum --> <!ENTITY uml "¨"> <!-- diaeresis = spacing diaeresis, U+00A8 ISOdia --> <!ENTITY copy "©"> <!-- copyright sign, U+00A9 ISOnum --> <!ENTITY ordf "ª"> <!-- feminine ordinal indicator, U+00AA ISOnum --> <!ENTITY laquo "«"> <!-- left-pointing double angle quotation mark = left pointing guillemet, U+00AB ISOnum --> <!ENTITY not "¬"> <!-- not sign = angled dash, U+00AC ISOnum --> <!ENTITY shy "­"> <!-- soft hyphen = discretionary hyphen, U+00AD ISOnum --> <!ENTITY reg "®"> <!-- registered sign = registered trade mark sign, U+00AE ISOnum --> <!ENTITY macr "¯"> <!-- macron = spacing macron = overline = APL overbar, U+00AF ISOdia --> <!ENTITY deg "°"> <!-- degree sign, U+00B0 ISOnum --> <!ENTITY plusmn "±"> <!-- plus-minus sign = plus-or-minus sign, U+00B1 ISOnum --> <!ENTITY sup2 "²"> <!-- superscript two = superscript digit two = squared, U+00B2 ISOnum --> <!ENTITY sup3 "³"> <!-- superscript three = superscript digit three = cubed, U+00B3 ISOnum --> <!ENTITY acute "´"> <!-- acute accent = spacing acute, U+00B4 ISOdia --> <!ENTITY micro "µ"> <!-- micro sign, U+00B5 ISOnum --> <!ENTITY para "¶"> <!-- pilcrow sign = paragraph sign, U+00B6 ISOnum --> <!ENTITY middot "·"> <!-- middle dot = Georgian comma = Greek middle dot, U+00B7 ISOnum --> <!ENTITY cedil "¸"> <!-- cedilla = spacing cedilla, U+00B8 ISOdia --> <!ENTITY sup1 "¹"> <!-- superscript one = superscript digit one, U+00B9 ISOnum --> <!ENTITY ordm "º"> <!-- masculine ordinal indicator, U+00BA ISOnum --> <!ENTITY raquo "»"> <!-- right-pointing double angle quotation mark = right pointing guillemet, U+00BB ISOnum --> <!ENTITY frac14 "¼"> <!-- vulgar fraction one quarter = fraction one quarter, U+00BC ISOnum --> <!ENTITY frac12 "½"> <!-- vulgar fraction one half = fraction one half, U+00BD ISOnum --> <!ENTITY frac34 "¾"> <!-- vulgar fraction three quarters = fraction three quarters, U+00BE ISOnum --> <!ENTITY iquest "¿"> <!-- inverted question mark = turned question mark, U+00BF ISOnum --> <!ENTITY Agrave "À"> <!-- latin capital letter A with grave = latin capital letter A grave, U+00C0 ISOlat1 --> <!ENTITY Aacute "Á"> <!-- latin capital letter A with acute, U+00C1 ISOlat1 --> <!ENTITY Acirc "Â"> <!-- latin capital letter A with circumflex, U+00C2 ISOlat1 --> <!ENTITY Atilde "Ã"> <!-- latin capital letter A with tilde, U+00C3 ISOlat1 --> <!ENTITY Auml "Ä"> <!-- latin capital letter A with diaeresis, U+00C4 ISOlat1 --> <!ENTITY Aring "Å"> <!-- latin capital letter A with ring above = latin capital letter A ring, U+00C5 ISOlat1 --> <!ENTITY AElig "Æ"> <!-- latin capital letter AE = latin capital ligature AE, U+00C6 ISOlat1 --> <!ENTITY Ccedil "Ç"> <!-- latin capital letter C with cedilla, U+00C7 ISOlat1 --> <!ENTITY Egrave "È"> <!-- latin capital letter E with grave, U+00C8 ISOlat1 --> <!ENTITY Eacute "É"> <!-- latin capital letter E with acute, U+00C9 ISOlat1 --> <!ENTITY Ecirc "Ê"> <!-- latin capital letter E with circumflex, U+00CA ISOlat1 --> <!ENTITY Euml "Ë"> <!-- latin capital letter E with diaeresis, U+00CB ISOlat1 --> <!ENTITY Igrave "Ì"> <!-- latin capital letter I with grave, U+00CC ISOlat1 --> <!ENTITY Iacute "Í"> <!-- latin capital letter I with acute, U+00CD ISOlat1 --> <!ENTITY Icirc "Î"> <!-- latin capital letter I with circumflex, U+00CE ISOlat1 --> <!ENTITY Iuml "Ï"> <!-- latin capital letter I with diaeresis, U+00CF ISOlat1 --> <!ENTITY ETH "Ð"> <!-- latin capital letter ETH, U+00D0 ISOlat1 --> <!ENTITY Ntilde "Ñ"> <!-- latin capital letter N with tilde, U+00D1 ISOlat1 --> <!ENTITY Ograve "Ò"> <!-- latin capital letter O with grave, U+00D2 ISOlat1 --> <!ENTITY Oacute "Ó"> <!-- latin capital letter O with acute, U+00D3 ISOlat1 --> <!ENTITY Ocirc "Ô"> <!-- latin capital letter O with circumflex, U+00D4 ISOlat1 --> <!ENTITY Otilde "Õ"> <!-- latin capital letter O with tilde, U+00D5 ISOlat1 --> <!ENTITY Ouml "Ö"> <!-- latin capital letter O with diaeresis, U+00D6 ISOlat1 --> <!ENTITY times "×"> <!-- multiplication sign, U+00D7 ISOnum --> <!ENTITY Oslash "Ø"> <!-- latin capital letter O with stroke = latin capital letter O slash, U+00D8 ISOlat1 --> <!ENTITY Ugrave "Ù"> <!-- latin capital letter U with grave, U+00D9 ISOlat1 --> <!ENTITY Uacute "Ú"> <!-- latin capital letter U with acute, U+00DA ISOlat1 --> <!ENTITY Ucirc "Û"> <!-- latin capital letter U with circumflex, U+00DB ISOlat1 --> <!ENTITY Uuml "Ü"> <!-- latin capital letter U with diaeresis, U+00DC ISOlat1 --> <!ENTITY Yacute "Ý"> <!-- latin capital letter Y with acute, U+00DD ISOlat1 --> <!ENTITY THORN "Þ"> <!-- latin capital letter THORN, U+00DE ISOlat1 --> <!ENTITY szlig "ß"> <!-- latin small letter sharp s = ess-zed, U+00DF ISOlat1 --> <!ENTITY agrave "à"> <!-- latin small letter a with grave = latin small letter a grave, U+00E0 ISOlat1 --> <!ENTITY aacute "á"> <!-- latin small letter a with acute, U+00E1 ISOlat1 --> <!ENTITY acirc "â"> <!-- latin small letter a with circumflex, U+00E2 ISOlat1 --> <!ENTITY atilde "ã"> <!-- latin small letter a with tilde, U+00E3 ISOlat1 --> <!ENTITY auml "ä"> <!-- latin small letter a with diaeresis, U+00E4 ISOlat1 --> <!ENTITY aring "å"> <!-- latin small letter a with ring above = latin small letter a ring, U+00E5 ISOlat1 --> <!ENTITY aelig "æ"> <!-- latin small letter ae = latin small ligature ae, U+00E6 ISOlat1 --> <!ENTITY ccedil "ç"> <!-- latin small letter c with cedilla, U+00E7 ISOlat1 --> <!ENTITY egrave "è"> <!-- latin small letter e with grave, U+00E8 ISOlat1 --> <!ENTITY eacute "é"> <!-- latin small letter e with acute, U+00E9 ISOlat1 --> <!ENTITY ecirc "ê"> <!-- latin small letter e with circumflex, U+00EA ISOlat1 --> <!ENTITY euml "ë"> <!-- latin small letter e with diaeresis, U+00EB ISOlat1 --> <!ENTITY igrave "ì"> <!-- latin small letter i with grave, U+00EC ISOlat1 --> <!ENTITY iacute "í"> <!-- latin small letter i with acute, U+00ED ISOlat1 --> <!ENTITY icirc "î"> <!-- latin small letter i with circumflex, U+00EE ISOlat1 --> <!ENTITY iuml "ï"> <!-- latin small letter i with diaeresis, U+00EF ISOlat1 --> <!ENTITY eth "ð"> <!-- latin small letter eth, U+00F0 ISOlat1 --> <!ENTITY ntilde "ñ"> <!-- latin small letter n with tilde, U+00F1 ISOlat1 --> <!ENTITY ograve "ò"> <!-- latin small letter o with grave, U+00F2 ISOlat1 --> <!ENTITY oacute "ó"> <!-- latin small letter o with acute, U+00F3 ISOlat1 --> <!ENTITY ocirc "ô"> <!-- latin small letter o with circumflex, U+00F4 ISOlat1 --> <!ENTITY otilde "õ"> <!-- latin small letter o with tilde, U+00F5 ISOlat1 --> <!ENTITY ouml "ö"> <!-- latin small letter o with diaeresis, U+00F6 ISOlat1 --> <!ENTITY divide "÷"> <!-- division sign, U+00F7 ISOnum --> <!ENTITY oslash "ø"> <!-- latin small letter o with stroke, = latin small letter o slash, U+00F8 ISOlat1 --> <!ENTITY ugrave "ù"> <!-- latin small letter u with grave, U+00F9 ISOlat1 --> <!ENTITY uacute "ú"> <!-- latin small letter u with acute, U+00FA ISOlat1 --> <!ENTITY ucirc "û"> <!-- latin small letter u with circumflex, U+00FB ISOlat1 --> <!ENTITY uuml "ü"> <!-- latin small letter u with diaeresis, U+00FC ISOlat1 --> <!ENTITY yacute "ý"> <!-- latin small letter y with acute, U+00FD ISOlat1 --> <!ENTITY thorn "þ"> <!-- latin small letter thorn, U+00FE ISOlat1 --> <!ENTITY yuml "ÿ"> <!-- latin small letter y with diaeresis, U+00FF ISOlat1 --> ]>';– 在這裡休息：
SELECT Question_Text FROM [stg].[Subform_Results] WHERE TRY_CONVERT(xml, @DTD + Question_Text, 2) IS NULL
只有那些包含有效XHTML的記錄才能成功TRY_CAST到 XML 文件中。
如果轉換成功，則返回一個轉換為指定數據類型的值；否則，返回 null。
在您的範例中，記錄 6571、6569 和 6570 似乎就是這種情況。
請注意，一旦您將記錄轉換為 XML，您的 XPath 查詢仍然需要引用 XML 文件中的有效路徑。您提供的範例路徑對您提供的任何範例文件均無效。
從記錄 6571、6569 和 6570 返回所需數據轉換的有效路徑是
.value('(/p/span/text())[1]','nvarchar(1000)'). 看到這個 db<>fiddle請注意區別 - 您還需要針對這些 XML 文件探究“span”。對於您想要探查的每個文件（即使是那些可轉換為 XML 的文件）而言，情況可能並非如此。
如果您需要幫助從 HTML 範例數據中抽查什麼是有效的 XML 或無效的 XML，如果您的範例數據可以安全地公開，您可以嘗試使用線上美化工具。正如您在下面看到的，它會為 XML 著色並添加空格，以使有效路徑對人眼更加明顯……
…或詳細說明您提供的文本無法轉換的原因。
除非您對源數據有更好的理解，否則解析的成功或失敗將視具體情況而定。儲存為 XML（以及 JSON）文件的數據可以是任意的，而整數或日期則不是。這可能很有用或（在您的情況下）很煩人。
你在這裡有兩個問題：
- 您有一些嵌套在
span節點中的值。無論嵌套多遠，您似乎實際上都想要*所有內部文本值。*你可以用.query('//text()').value('text()[1]','nvarchar(max)')這個。- 您的數據實際上是格式良好的 XHTML 片段。這裡的關鍵詞是Fragments，它不包含相關的 DTD 規範，因此 SQL Server 可以翻譯
. 即使它確實指定了 DTD，SQL Server 也不會下載外部規範。您可以改為手動添加完整規範。如果您覺得您實際上並不需要所有這些實體，您可以刪除那些與您的數據無關的實體。
DECLARE @dtd nvarchar(max) = N' <!DOCTYPE xhtml [ <!ENTITY nbsp " "> <!-- no-break space = non-breaking space, U+00A0 ISOnum --> <!ENTITY iexcl "¡"> <!-- inverted exclamation mark, U+00A1 ISOnum --> <!ENTITY cent "¢"> <!-- cent sign, U+00A2 ISOnum --> <!ENTITY pound "£"> <!-- pound sign, U+00A3 ISOnum --> <!ENTITY curren "¤"> <!-- currency sign, U+00A4 ISOnum --> <!ENTITY yen "¥"> <!-- yen sign = yuan sign, U+00A5 ISOnum --> <!ENTITY brvbar "¦"> <!-- broken bar = broken vertical bar, U+00A6 ISOnum --> <!ENTITY sect "§"> <!-- section sign, U+00A7 ISOnum --> <!ENTITY uml "¨"> <!-- diaeresis = spacing diaeresis, U+00A8 ISOdia --> <!ENTITY copy "©"> <!-- copyright sign, U+00A9 ISOnum --> <!ENTITY ordf "ª"> <!-- feminine ordinal indicator, U+00AA ISOnum --> <!ENTITY laquo "«"> <!-- left-pointing double angle quotation mark = left pointing guillemet, U+00AB ISOnum --> <!ENTITY not "¬"> <!-- not sign = angled dash, U+00AC ISOnum --> <!ENTITY shy "­"> <!-- soft hyphen = discretionary hyphen, U+00AD ISOnum --> <!ENTITY reg "®"> <!-- registered sign = registered trade mark sign, U+00AE ISOnum --> <!ENTITY macr "¯"> <!-- macron = spacing macron = overline = APL overbar, U+00AF ISOdia --> <!ENTITY deg "°"> <!-- degree sign, U+00B0 ISOnum --> <!ENTITY plusmn "±"> <!-- plus-minus sign = plus-or-minus sign, U+00B1 ISOnum --> <!ENTITY sup2 "²"> <!-- superscript two = superscript digit two = squared, U+00B2 ISOnum --> <!ENTITY sup3 "³"> <!-- superscript three = superscript digit three = cubed, U+00B3 ISOnum --> <!ENTITY acute "´"> <!-- acute accent = spacing acute, U+00B4 ISOdia --> <!ENTITY micro "µ"> <!-- micro sign, U+00B5 ISOnum --> <!ENTITY para "¶"> <!-- pilcrow sign = paragraph sign, U+00B6 ISOnum --> <!ENTITY middot "·"> <!-- middle dot = Georgian comma = Greek middle dot, U+00B7 ISOnum --> <!ENTITY cedil "¸"> <!-- cedilla = spacing cedilla, U+00B8 ISOdia --> <!ENTITY sup1 "¹"> <!-- superscript one = superscript digit one, U+00B9 ISOnum --> <!ENTITY ordm "º"> <!-- masculine ordinal indicator, U+00BA ISOnum --> <!ENTITY raquo "»"> <!-- right-pointing double angle quotation mark = right pointing guillemet, U+00BB ISOnum --> <!ENTITY frac14 "¼"> <!-- vulgar fraction one quarter = fraction one quarter, U+00BC ISOnum --> <!ENTITY frac12 "½"> <!-- vulgar fraction one half = fraction one half, U+00BD ISOnum --> <!ENTITY frac34 "¾"> <!-- vulgar fraction three quarters = fraction three quarters, U+00BE ISOnum --> <!ENTITY iquest "¿"> <!-- inverted question mark = turned question mark, U+00BF ISOnum --> <!ENTITY Agrave "À"> <!-- latin capital letter A with grave = latin capital letter A grave, U+00C0 ISOlat1 --> <!ENTITY Aacute "Á"> <!-- latin capital letter A with acute, U+00C1 ISOlat1 --> <!ENTITY Acirc "Â"> <!-- latin capital letter A with circumflex, U+00C2 ISOlat1 --> <!ENTITY Atilde "Ã"> <!-- latin capital letter A with tilde, U+00C3 ISOlat1 --> <!ENTITY Auml "Ä"> <!-- latin capital letter A with diaeresis, U+00C4 ISOlat1 --> <!ENTITY Aring "Å"> <!-- latin capital letter A with ring above = latin capital letter A ring, U+00C5 ISOlat1 --> <!ENTITY AElig "Æ"> <!-- latin capital letter AE = latin capital ligature AE, U+00C6 ISOlat1 --> <!ENTITY Ccedil "Ç"> <!-- latin capital letter C with cedilla, U+00C7 ISOlat1 --> <!ENTITY Egrave "È"> <!-- latin capital letter E with grave, U+00C8 ISOlat1 --> <!ENTITY Eacute "É"> <!-- latin capital letter E with acute, U+00C9 ISOlat1 --> <!ENTITY Ecirc "Ê"> <!-- latin capital letter E with circumflex, U+00CA ISOlat1 --> <!ENTITY Euml "Ë"> <!-- latin capital letter E with diaeresis, U+00CB ISOlat1 --> <!ENTITY Igrave "Ì"> <!-- latin capital letter I with grave, U+00CC ISOlat1 --> <!ENTITY Iacute "Í"> <!-- latin capital letter I with acute, U+00CD ISOlat1 --> <!ENTITY Icirc "Î"> <!-- latin capital letter I with circumflex, U+00CE ISOlat1 --> <!ENTITY Iuml "Ï"> <!-- latin capital letter I with diaeresis, U+00CF ISOlat1 --> <!ENTITY ETH "Ð"> <!-- latin capital letter ETH, U+00D0 ISOlat1 --> <!ENTITY Ntilde "Ñ"> <!-- latin capital letter N with tilde, U+00D1 ISOlat1 --> <!ENTITY Ograve "Ò"> <!-- latin capital letter O with grave, U+00D2 ISOlat1 --> <!ENTITY Oacute "Ó"> <!-- latin capital letter O with acute, U+00D3 ISOlat1 --> <!ENTITY Ocirc "Ô"> <!-- latin capital letter O with circumflex, U+00D4 ISOlat1 --> <!ENTITY Otilde "Õ"> <!-- latin capital letter O with tilde, U+00D5 ISOlat1 --> <!ENTITY Ouml "Ö"> <!-- latin capital letter O with diaeresis, U+00D6 ISOlat1 --> <!ENTITY times "×"> <!-- multiplication sign, U+00D7 ISOnum --> <!ENTITY Oslash "Ø"> <!-- latin capital letter O with stroke = latin capital letter O slash, U+00D8 ISOlat1 --> <!ENTITY Ugrave "Ù"> <!-- latin capital letter U with grave, U+00D9 ISOlat1 --> <!ENTITY Uacute "Ú"> <!-- latin capital letter U with acute, U+00DA ISOlat1 --> <!ENTITY Ucirc "Û"> <!-- latin capital letter U with circumflex, U+00DB ISOlat1 --> <!ENTITY Uuml "Ü"> <!-- latin capital letter U with diaeresis, U+00DC ISOlat1 --> <!ENTITY Yacute "Ý"> <!-- latin capital letter Y with acute, U+00DD ISOlat1 --> <!ENTITY THORN "Þ"> <!-- latin capital letter THORN, U+00DE ISOlat1 --> <!ENTITY szlig "ß"> <!-- latin small letter sharp s = ess-zed, U+00DF ISOlat1 --> <!ENTITY agrave "à"> <!-- latin small letter a with grave = latin small letter a grave, U+00E0 ISOlat1 --> <!ENTITY aacute "á"> <!-- latin small letter a with acute, U+00E1 ISOlat1 --> <!ENTITY acirc "â"> <!-- latin small letter a with circumflex, U+00E2 ISOlat1 --> <!ENTITY atilde "ã"> <!-- latin small letter a with tilde, U+00E3 ISOlat1 --> <!ENTITY auml "ä"> <!-- latin small letter a with diaeresis, U+00E4 ISOlat1 --> <!ENTITY aring "å"> <!-- latin small letter a with ring above = latin small letter a ring, U+00E5 ISOlat1 --> <!ENTITY aelig "æ"> <!-- latin small letter ae = latin small ligature ae, U+00E6 ISOlat1 --> <!ENTITY ccedil "ç"> <!-- latin small letter c with cedilla, U+00E7 ISOlat1 --> <!ENTITY egrave "è"> <!-- latin small letter e with grave, U+00E8 ISOlat1 --> <!ENTITY eacute "é"> <!-- latin small letter e with acute, U+00E9 ISOlat1 --> <!ENTITY ecirc "ê"> <!-- latin small letter e with circumflex, U+00EA ISOlat1 --> <!ENTITY euml "ë"> <!-- latin small letter e with diaeresis, U+00EB ISOlat1 --> <!ENTITY igrave "ì"> <!-- latin small letter i with grave, U+00EC ISOlat1 --> <!ENTITY iacute "í"> <!-- latin small letter i with acute, U+00ED ISOlat1 --> <!ENTITY icirc "î"> <!-- latin small letter i with circumflex, U+00EE ISOlat1 --> <!ENTITY iuml "ï"> <!-- latin small letter i with diaeresis, U+00EF ISOlat1 --> <!ENTITY eth "ð"> <!-- latin small letter eth, U+00F0 ISOlat1 --> <!ENTITY ntilde "ñ"> <!-- latin small letter n with tilde, U+00F1 ISOlat1 --> <!ENTITY ograve "ò"> <!-- latin small letter o with grave, U+00F2 ISOlat1 --> <!ENTITY oacute "ó"> <!-- latin small letter o with acute, U+00F3 ISOlat1 --> <!ENTITY ocirc "ô"> <!-- latin small letter o with circumflex, U+00F4 ISOlat1 --> <!ENTITY otilde "õ"> <!-- latin small letter o with tilde, U+00F5 ISOlat1 --> <!ENTITY ouml "ö"> <!-- latin small letter o with diaeresis, U+00F6 ISOlat1 --> <!ENTITY divide "÷"> <!-- division sign, U+00F7 ISOnum --> <!ENTITY oslash "ø"> <!-- latin small letter o with stroke, = latin small letter o slash, U+00F8 ISOlat1 --> <!ENTITY ugrave "ù"> <!-- latin small letter u with grave, U+00F9 ISOlat1 --> <!ENTITY uacute "ú"> <!-- latin small letter u with acute, U+00FA ISOlat1 --> <!ENTITY ucirc "û"> <!-- latin small letter u with circumflex, U+00FB ISOlat1 --> <!ENTITY uuml "ü"> <!-- latin small letter u with diaeresis, U+00FC ISOlat1 --> <!ENTITY yacute "ý"> <!-- latin small letter y with acute, U+00FD ISOlat1 --> <!ENTITY thorn "þ"> <!-- latin small letter thorn, U+00FE ISOlat1 --> <!ENTITY yuml "ÿ"> <!-- latin small letter y with diaeresis, U+00FF ISOlat1 --> ]>' SELECT CONVERT(xml, @DTD + Question_Text, 2).query('//text()').value('text()[1]','nvarchar(max)') FROM tblQuestionsSample