Вопрос:
Третий компонент заполняет столбец nvarchar в таблице с некоторыми значениями. В большинстве случаев это удобочитаемая строка, но иногда это XML (в случае некоторых внутренних исключений в стороннем comp).
Как временное решение (до тех пор, пока они не исправят его и не будут использовать строку всегда), я хотел бы проанализировать данные XML и извлечь фактическое сообщение.
Среда: SQL Server 2005; строки всегда меньше 1 КБ; в этой таблице может быть несколько тысяч строк.
Я столкнулся с несколькими решениями, но я не уверен, что они достаточно хороши:
- Вызвать sp_xml_preparedocument сохраненную proc и обернуть ее вокруг блока TRY/CATCH. Проверьте возвращаемое значение/ручку.
- Записать управляемый код (в С#), снова обработать исключение и посмотреть, является ли он допустимой строкой.
Ни один из этих методов не кажется эффективным. Я искал нечто похожее на ISNUMERIC(): a ISXML(). Есть ли другой лучший способ проверки строки?
Лучший ответ:
Я хотел бы проанализировать данные XML и извлечь фактическое сообщение.
Возможно, нет необходимости проверять правильность XML. Вы можете проверить наличие соответствующего тега xml с помощью charindex в инструкции case и извлечь сообщение об ошибке с помощью substring.
Вот пример с упрощенной XML-строкой, но я думаю, что вы поняли эту идею.
declare @T table(ID int, Col1 nvarchar(1000)) insert into @T values (1, ‘No xml value 1’), (2, ‘No xml value 2’), (3, ‘<root><item>Text value in xml</item></root>’) select case when charindex(‘<item>’, Col1) = 0 then Col1 else substring(Col1, charindex(‘<item>’, Col1)+6, charindex(‘</item>’, Col1)-charindex(‘<item>’, Col1)-6) end from @T
Результат:
No xml value 1 No xml value 2 Text value in xml Ответ №1
Вы можете создать XML-схему и использовать ее для проверки на строках XML.
См. дополнительную информацию: http://msdn.microsoft.com/en-us/library/ms176009.aspx
Вот пример:
CREATE XML SCHEMA COLLECTION UserSchemaCollection AS N'<?xml version=»1.0″ encoding=»UTF-16″?> <xsd:schema xmlns:xsd=»http://www.w3.org/2001/XMLSchema»> <xsd:element name = «User» > <xsd:complexType> <xsd:sequence> <xsd:element name = «UserID» /> <xsd:element name = «UserName» /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>’; DECLARE @x XML(UserSchemaCollection) SELECT @x = ‘<User><UserID>1234</UserID><UserName>Sebastian</UserName></User>’
Примеры:
DECLARE @y XML(UserSchemaCollection) SELECT @y = ‘<User><UserName>Sebastian</UserName></User>’
Msg 6965, уровень 16, состояние 1, строка 2
Проверка XML: недопустимый контент. Ожидаемый элемент (ы): UserID, где указан элемент “UserName”. Местоположение:/: Пользователь [1]/: UserName [1]
DECLARE @z XML(UserSchemaCollection) SELECT @z = ‘Some text’
Msg 6909, уровень 16, состояние 1, строка 2
Проверка XML: текст node в этом месте не разрешен, тип был определен только содержимым элемента или простым контентом. Местоположение:/
Ответ №2
На основании принятого ответа я создал это, чтобы проверить допустимый XML и, возможно, преобразовать входную строку в XML (или извлечь необходимые элементы/атрибуты из XML), потому что я понял, что TRY_CONVERT работает успешно, если вы просто передаете обычный текст, чего я не ожидал, поэтому потребовалась еще одна проверка, чтобы предотвратить окончательное преобразование в XML Мне нужно работать, если исходный столбец просто содержит некоторый текст (пример строки 1):
declare @T table(ID int, Col1 nvarchar(1000)) insert into @T values (1, ‘random text value 1’), (2, ‘<broken> or invalid xml value 2’), (3, ‘<root><item>valid xml</item></root>’) select id, Col1, Converted_XML = CASE when [Col1] IS NULL THEN NULL /* NULL stays NULL */ when TRY_CONVERT(xml, [Col1]) is null THEN NULL /* Xml Document Error */ when CHARINDEX(‘<‘, [Col1]) < 1 AND CHARINDEX(‘>’, [Col1]) < 1 THEN NULL /* no xml */ else CONVERT(xml, [Col1]) /* Parsing succesful. => in this case you can convert string to XML and/or extract the values */ END, Result_Comment = CASE when [Col1] IS NULL THEN ‘NULL always stays NULL’ when TRY_CONVERT(xml, [Col1]) is null THEN ‘Xml Document Error’ when CHARINDEX(‘<‘, [Col1]) < 1 AND CHARINDEX(‘>’, [Col1]) < 1 THEN ‘no xml’ else [Col1] END FROM @T ; Ответ №3
Я не знаю о лучшем способе, но вот способ:
DECLARE @table TABLE (myXML XML) INSERT INTO @table SELECT ‘ <Employee> <FirstName>Henry</FirstName> <LastName>Ford</LastName> </Employee> ‘ SELECT myXML FROM @table FOR XML RAW
Если XML недействителен, он выдает ошибку:
DECLARE @table TABLE (myXML XML) INSERT INTO @table SELECT ‘ <Employee <FirstName>Henry</FirstName> <LastName>Ford</LastName> </Employee> ‘ SELECT myXML FROM @table FOR XML RAW
Просто для разъяснения, все, что вам нужно сделать, это бросить его:
BEGIN TRY DECLARE @myXML XML SET @myXML = CAST (‘ <Employee> <FirstName>Henry</FirstName> <LastName>Ford</LastName> </Employee> ‘ AS XML) SELECT ‘VALID XML’ END TRY BEGIN CATCH SELECT ‘INVALID XML’ END CATCH;
против
BEGIN TRY DECLARE @myXML XML SET @myXML = CAST (‘ <Employee <FirstName>Henry</FirstName> <LastName>Ford</LastName> </Employee> ‘ AS XML) SELECT ‘VALID XML’ END TRY BEGIN CATCH SELECT ‘INVALID XML’ END CATCH;