Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Конвертация NVARCHAR(MAX) в XML  [new]
Anatoliy
Member

Откуда: Kiev
Сообщений: 100
Есть таблица, содержащая поле типа NVARCHAR(MAX) CustomData, содержащая строки типа
<object><ConversationList><Conversation userid="1" date="2012-08-27 13:51:43Z" text="First message" /><Conversation userid="2" date="2012-08-27 13:51:43Z" text="Second message wit left angle &lt;" /></ConversationList></object>

Или таже строка с переносами строк и отступами:
<object>
<ConversationList>
<Conversation userid="1" date="2012-08-27 13:51:43Z" text="First message" />
<Conversation userid="2" date="2012-08-27 13:51:43Z" text="Second message wit left angle &lt;" />
</ConversationList>
</object>

Чтобы привести ее в XML формат, я не придумал ничего лучшего, как выполнять замену:
SET @CustomData = 
	REPLACE(
	REPLACE(
	REPLACE(
	REPLACE(
	REPLACE(@CustomData
	, '&amp;', '&')
	, '&apos;', '''')
	, '&quot;', '"')
	, '&gt;', '>')
	, '&lt;', '<')

На выходе получаю строку типа
<object><ConversationList><Conversation userid="1" date="2012-08-27 13:51:43Z" text="First message" /><Conversation userid="2" date="2012-08-27 13:51:43Z" text="Second message wit left angle <" /></ConversationList></object>

или с отступами:
<object>
	<ConversationList>
		<Conversation userid="1" date="2012-08-27 13:51:43Z" text="First message" />
		<Conversation userid="2" date="2012-08-27 13:51:43Z" text="Second message wit left angle <" />
	</ConversationList>
</object>
Вопрос: нет ли в SQL Server встроенной функции, которая выполняла бы такую конвертацию?
27 авг 12, 19:11    [13072100]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Anatoliy,

Так у вас и так уже нормальный XML. Зачем избавляться от escape-последовательностей?
27 авг 12, 19:47    [13072230]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
Anatoliy
Member

Откуда: Kiev
Сообщений: 100
Дело в том, что проблема лежит немного глубже.
Пока пользователи не использовали в своих сообщениях специальные символы типа "<", ">", "&" все было замечательно. Но, если в сообщении присутсвует один из подобных символов. Например, во втором сообщении с реальным текстом
"Second message with left angle <" 

в базе текст сохраняется в следующем виде:
"Second message with left angle &amp;lt"


И теперь, при моей замене (серия из 5-ти REPLACE) автоматически меняется и текст на следующий:
"Second message with left angle &lt"


Попробую выложить часть проблемного кода из моей UDF, который валится, если в тексте присутсвуют спецсимволы.

DECLARE @CustomData NVARCHAR(MAX) 
SET @CustomData = 
'<object>' + 
	'<ConversationList>' +
		'<Conversation userid="1" date="2012-08-27 13:51:43Z" text="First message" />' + 
		'<Conversation userid="2" date="2012-08-27 13:51:43Z" text="Second message with left angle &amp;lt" />' + 
	'</ConversationList>' + 
'</object>'

		SET @CustomData = 
			REPLACE(
			REPLACE(
			REPLACE(
			REPLACE(
			REPLACE(@CustomData
			, '&amp;', '&')
			, '&apos;', '''')
			, '&quot;', '"')
			, '&gt;', '>')
			, '&lt;', '<')



		DECLARE @Convers XML
                            -- Вот здесь будет ошибка, если в тексте сообщения присутсвует спецсимвол.
		SET @Convers = CAST(@CustomData AS XML)
		

		DECLARE @tempConversationsDate TABLE
		(
			Id INT NOT NULL IDENTITY (1, 1)
			, DateConversation NVARCHAR(30) 
		)

		INSERT INTO @tempConversationsDate(DateConversation)
		SELECT CASE WHEN LEN(Dt.DateString) > 0 AND LOWER(SUBSTRING(Dt.DateString, LEN(Dt.DateString), 1)) = 'z' 
			THEN LEFT(Dt.DateString, LEN(Dt.DateString)-1) 
			ELSE Dt.DateString 
			END 
		FROM (
				SELECT l.d.value('.', 'nvarchar(30)') AS DateString
				FROM @Convers.nodes('//Conversation/@date') l(d)
			) AS Dt


		DECLARE @tempConversationsText TABLE
		(
			Id INT NOT NULL IDENTITY (1, 1)
			, DateConversation NVARCHAR(30) 
			, TextConversation NVARCHAR(MAX) 
		)


		INSERT INTO @tempConversationsText(TextConversation)
		SELECT l.t.value('.','varchar(MAX)')
		FROM @Convers.nodes('//Conversation/@text') l(t)

		UPDATE @tempConversationsText 
		SET DateConversation = d.DateConversation
		FROM @tempConversationsText AS t INNER JOIN @tempConversationsDate AS d ON t.Id = d.Id 

		
		SELECT * FROM @tempConversationsText


Если вы уберете из исходного текста
"&amp;lt" 

скрипт будет выполняться на ура.

Вот поэтому я и думал, может есть что-то специально встроенная функция (или другая возможность) конвертации строки в XML вместо моего самодельного пятикратного REPLACE, которая не затрагивала бы внутренний текст сообщений. Как-то так...
28 авг 12, 10:12    [13073930]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
mike909
Member

Откуда:
Сообщений: 662
Anatoliy,

declare @str nvarchar(max), @xml xml

set @str = '<<< Bla Bla Bla >>>'

select @xml = (
  select @str
  for xml path(''), type
)

select @xml

select @xml.value('(text())[1]','varchar(max)')

?
28 авг 12, 10:28    [13074022]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Anatoliy,

Разбирайтесь с кодом, который у вас пишет xml в БД. Потому что для
"Second message with left angle <"
должно быть
"Second message with left angle &lt;"
а не
"Second message with left angle &amp;lt"
28 авг 12, 10:30    [13074032]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
Anatoliy
Member

Откуда: Kiev
Сообщений: 100
invm
Anatoliy,

Разбирайтесь с кодом, который у вас пишет xml в БД

Разбираться то можно. Но это длинный и неблагодарный путь. Поскольку подобные записи Conversation сохранялись на протяжении нескольких лет у многих конечных пользователей нашей программы. Поэтому надо будет:
1. Написать скрипт, который бы нашел бы во всех записях вхождения
"&amp;lt", "&amp;gt", ....
и заменил бы на необходимые.
2. Проверить, что данный скрипт ни при каких обстоятельствах не поломает остальную информацию.
3. Выполнить данный скрипт на базах всех конечных пользователей

Поэтому лучше и проще попытаться пойти более простым, и безопасным путем. Исходные данные в таблице оставить как есть. А вот в самой UDF извернуться таким образом, чтобы все вхождения типа
"&lt;", "&gt;" ...

Поменять на
"<", ">", ...

и при этом вхождения типа
"&amp;lt;", "&amp;gt;" ...

оставить как есть. И только на выходе из функции (в самой результирующей таблице @tempConversationsDate) выполнить UPDATE, чтобы заменить все
"&amp;lt;", "&amp;gt;" ...

на
"<", ">", ...


Это я так вижу решение проблемы. Может кто свежим взглядом подкинет более простое решение.
28 авг 12, 12:45    [13075208]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Anatoliy,

Вы понимаете, что нельзя в данных напрямую использовать служебные символы?
28 авг 12, 14:05    [13075966]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
Anatoliy
Member

Откуда: Kiev
Сообщений: 100
invm
Anatoliy,

Вы понимаете, что нельзя в данных напрямую использовать служебные символы?


Тип поля NVARCHAR(MAX), а не XML.
Как видите, что хранить в нем можно любые символы :)
Вот надо ли??? Это уже скорее риторический вопрос.
Но это уже вопросы проработки архитектуры приложения, чтобы суметь предусмотреть если не все, то большинство ньюансов таких узких мест.
Другой вопрос, к каким сложностям это привело на данный момент. И какие еще могут проявиться в будущем???

А пока попробую используя данные как есть (без каких либо UPDATE), извлечь из них необходимую информацию.
28 авг 12, 14:34    [13076254]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Есть такие две крайности программистской глупости:
1. Боязнь менять что либо
2. Переписывание ради эфемерного "сделать круче"

Чего так сложно ALTER TABLE сделать?
28 авг 12, 16:06    [13077194]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
Anatoliy
Member

Откуда: Kiev
Сообщений: 100
ALTER TABLE совершенно не решает проблему.
Если итдти таким путем, то:
1. Необходимо поменять логику сохранения данных на клиенте в базу данных.
2. Необходимо проапдейтить сотни тысяч записей на десятках баз данных конечных пользователей.

Что еще понадобится передалать, надо хорошо пересмотреть огромный солюшен. Где это все может вылезти боком.
Дело в том, что ALTER TABLE в базе, которая существует в единственном экземпляре и тот же ALTER TABLE на десятках баз у разных пользователей, в разных городах и даже странах, сильно отличается по трудоемкости. Да и последствия бывают не всегда предсказуемыми.

Задача стояла не изменяя исходных сохраненных данных, вытащить из них необходимую информацию.

В общем решил может не самым изящным образом...
Сначала делаю REPLACE содержащихся в строке символов типа
"&amp;lt;", "&amp;gt;" ...

На что-то, что наверняка не встретится в обыкновенном Conversation (у меня это слово StrangeConst, написанное в обратном порядке (REVERSE('StrangeConst')) + ASCII код служебного символа, содержащегося в тексте Conversation. Можно использовать вместо REVERSE('StrangeConst') GUID или еще что-то в этом роде.
Потом выполняю свою 5-кратную замену REPLACE.

А в самом конце выполнения кода UDF строки типа 'tsnoCegnartS60' меняю на '<', etc (опять же похожим пятикратным REPLACE).

		-- before main replace need change strings like '&amp;lt;' to 'tsnoCegnartS60'
		DECLARE @StrangeConst NVARCHAR(20)
		SET @StrangeConst = REVERSE('StrangeConst')
		-- SELECT @StrangeConst, LEN(@StrangeConst) 
		
		SET @CustomData = 
			REPLACE(
			REPLACE(
			REPLACE(
			REPLACE(
			REPLACE(@CustomData
			, '&amp;amp;', @StrangeConst + LTRIM(STR(ASCII('&'))))
			, '&amp;apos;', @StrangeConst + LTRIM(STR(ASCII(''''))))
			, '&amp;quot;', @StrangeConst + LTRIM(STR(ASCII('"'))))
			, '&amp;gt;', @StrangeConst + LTRIM(STR(ASCII('>'))))
			, '&amp;lt;', @StrangeConst + LTRIM(STR(ASCII('<')))) 
		
		-- replace escape secuenses to reserverd symbols in the string @CustomData
		SET @CustomData = 
			REPLACE(
			REPLACE(
			REPLACE(
			REPLACE(
			REPLACE(@CustomData
			, '&amp;', '&')
			, '&apos;', '''')
			, '&quot;', '"')
			, '&gt;', '>')
			, '&lt;', '<')

		DECLARE @Convers XML
		SET @Convers = CAST(@CustomData AS XML)

		DECLARE @tempConversationsDate TABLE
		(
			Id INT NOT NULL IDENTITY (1, 1)
			, DateConversation NVARCHAR(30) 
		)

		INSERT INTO @tempConversationsDate(DateConversation)
		SELECT CASE WHEN LEN(Dt.DateString) > 0 AND LOWER(SUBSTRING(Dt.DateString, LEN(Dt.DateString), 1)) = 'z' 
			THEN LEFT(Dt.DateString, LEN(Dt.DateString)-1) 
			ELSE Dt.DateString 
			END 
		FROM (
				SELECT l.d.value('.', 'nvarchar(30)') AS DateString
				FROM @Convers.nodes('//Conversation/@date') l(d)
			) AS Dt


		DECLARE @tempConversationsText TABLE
		(
			Id INT NOT NULL IDENTITY (1, 1)
			, DateConversation NVARCHAR(30) 
			, TextConversation NVARCHAR(MAX) 
		)


		INSERT INTO @tempConversationsText(TextConversation)
		SELECT l.t.value('.','varchar(MAX)')
		FROM @Convers.nodes('//Conversation/@text') l(t)

		UPDATE @tempConversationsText 
		SET DateConversation = d.DateConversation
		FROM @tempConversationsText AS t INNER JOIN @tempConversationsDate AS d ON t.Id = d.Id 

		-- replace backwards
		UPDATE @tempConversationsText 
		SET TextConversation = 
			REPLACE(
			REPLACE(
			REPLACE(
			REPLACE(
			REPLACE(TextConversation
			, @StrangeConst + LTRIM(STR(ASCII('&'))),  '&') 
			, @StrangeConst + LTRIM(STR(ASCII(''''))), '''') 
			, @StrangeConst + LTRIM(STR(ASCII('"'))), '"') 
			, @StrangeConst + LTRIM(STR(ASCII('>'))), '>') 
			, @StrangeConst + LTRIM(STR(ASCII('<'))), '<') 
		FROM @tempConversationsText 

Даже не вникая в код, можно заметить пятикратные REPLACE вначале кода (прямая замена) и опять же пятикратные REPLACE вконце кода (обратная замена).

Пробовал на самых разнообразных самых диких сочетаниях юникодовских символов (в первую очередь на '<', '>', '&', ... etc.)
Все срабатывает.
28 авг 12, 16:49    [13077602]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Сегодня вы напишите один костыль, завтра другой уже со скрипом зубов, а после завтра нужно быстро быстро что-то сделать и всё хамба. Так точно будет, уверяю вас.
Лучше проверить как отразится XML поле, может там ничего делать и не придётся особого, а если и придётся, чё тяжело чтоле поправить.

Ну это вы сами решайте. Лучше способ, при котором работает в обоиз случаях, а там можно дождаться когда приложение нужно палюбэ менять.

А говорят независимость, база чисто хранилище, вон, "логика" на клиенте, а костыли на сервере. Ад.
Если логика на клиенте, он должен быть настолько же гибок, чтоб быстро исправлять баги. А так кандалы.

Стратэги. Манёвры.
28 авг 12, 17:10    [13077806]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Anatoliy,

И как ваш монструозный скрипт отработает вот на таких данных:
declare @CustomData varchar(max) =
'<object>' + 
	'<ConversationList>' +
		'<Conversation userid="1" date="2012-08-27 13:51:43Z" text="First message" />' + 
		'<Conversation userid="2" date="2012-08-27 13:51:43Z" text="Second message with left angle &amp;lt; &lt;" />' + 
	'</ConversationList>' + 
'</object>';
?

Вместо всех ваших странных плясок, надо просто привести строку к валидному xml:
declare @CustomData varchar(max) =
'<object>' + 
	'<ConversationList>' +
		'<Conversation userid="1" date="2012-08-27 13:51:43Z" text="First message" />' + 
		'<Conversation userid="2" date="2012-08-27 13:51:43Z" text="Second message with left angle &amp;lt; &lt;" />' + 
	'</ConversationList>' + 
'</object>';

select @CustomData = 
 replace(
 replace(
 replace(
 replace(
 replace(@CustomData
 , '&amp;amp;', '&amp;')
 , '&amp;apos;', '&apos;')
 , '&amp;quot;', '&quot;')
 , '&amp;gt;', '&gt;')
 , '&amp;lt;', '&lt;') 

declare @x xml = @CustomData;

select @x;

with cte(d, t) as
(
 select
  replace(replace(n.value('@date', 'varchar(30)'), 'Z', ''), ' ', 'T'),
  n.value('@text', 'varchar(max)')
 from
  @x.nodes('/object/ConversationList/Conversation') t(n)
)
select
 cast(case when isdate(d) = 1 then d else null end as datetime),
 t
from
 cte;
28 авг 12, 17:49    [13078075]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
Anatoliy
Member

Откуда: Kiev
Сообщений: 100
Спасибо. Попробую прикрутить к своей функции.
29 авг 12, 09:47    [13079901]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
пролетевший
Member

Откуда: от Михал Иваныча
Сообщений: 1314
Anatoliy
На выходе получаю строку типа

<object>
	<ConversationList>
		<Conversation userid="1" date="2012-08-27 13:51:43Z" text="First message" />
		<Conversation userid="2" date="2012-08-27 13:51:43Z" text="Second message wit left angle <" />
	</ConversationList>
</object>

А вы понимаете что результат не является XML ? если эти данные где нибудь попадают в любой XML парсер, он просто скажет что формат неправильный и откажется работать.
Исходный вариант соответствует требованиям XML, символы & < и > заменены на entity.
29 авг 12, 11:28    [13080788]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация NVARCHAR(MAX) в XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Тут вообще нет никакой проблемы. Не надо никаких Replace-ов.
DECLARE	@CustomData	VarChar(max) =
'<object>' + 
	'<ConversationList>' +
		'<Conversation userid="1" date="2012-08-27 13:51:43Z" text="First message" />' + 
		'<Conversation userid="2" date="2012-08-27 13:51:43Z" text="Second message with left angle &amp;lt;" />' + 
	'</ConversationList>' + 
'</object>';

DECLARE	@XML	XML	= @CustomData;
SELECT	 X.value('@date','DateTime')
	,Convert(XML,X.value('@text','varchar(max)')).value('text()[1]','varchar(max)')
FROM	@XML.nodes('/object/ConversationList/Conversation') X(X)
datetext
2012-08-27 13:51:43.000First message
2012-08-27 13:51:43.000Second message with left angle <
29 авг 12, 13:03    [13081705]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить