Функция SQL REPLACE внутри Xml.modify 'заменить значение'
Я пытаюсь обновить некоторые данные XML в SQL Server. XML содержит данные, которые выглядят следующим образом:
<root> <id>1</id> <timestamp>16-10-2017 19:24:55</timestamp> </root> Предположим, что этот XML существует в столбце Data в таблице TestTable . Я хотел бы иметь возможность изменить дефисы в метке времени на косые черты.
Я надеялся, что смогу сделать что-то вроде:
- MSSQL Query Xml разностный порядок
- XPath с SQL Server без пространства имен
- Оценка выражений XQuery / XPath из таблицы
- Использовать XML-запрос как часть предиката WHERE
- Xquery для анализа тегов XML в столбцах
update TestTable set Data.modify('replace value of (/root/timestamp/text())[1] with REPLACE((/root/timestamp/text())[1], "-", "/")')
Я получаю следующую ошибку:
XQuery [TestTable]: нет функции '{ http://www.w3.org/2004/07/xpath-functions }: REPLACE ()'
Когда я думаю об этом, это имеет смысл. Но мне интересно, есть ли способ сделать это в одном обновлении? Или мне сначала нужно запросить значение временной метки и сохранить его как переменную, а затем обновить XML с помощью переменной?
- Поиск различий между двумя строками в SQL
- Запрос XPath: анализ нескольких путей с использованием одного и того же запроса (Cross Apply / .nodes ())
- Запрос XPATH для получения данных из XML, хранящихся в столбце базы данных SQL Server
- Как получить значения элементов из столбца XML?
- XML DML (Xpath), чтобы запретить столбцы dupliacte. Он должен проверить, существует ли столбец, прежде чем вставлять в него
- Как читать значения XML в SQL Server
- Как сделать XML-значения разделенными запятыми с помощью XPath, XQuery в SQL Server
- Извлечение значений из двухуровневого XML-списка в колонку SQL
Примечание: этот ответ предполагает, что вы хотите, чтобы этот формат был отформатирован с целью отображения этого в виде строки, а не для разбора содержимого как xs: dateTime. Если вы хотите последнего, ответ Shungo будет отформатировать его как таковой.
Похоже, что replace не является поддерживаемой функцией XQuery в SQL Server на момент написания этой статьи. Вы можете использовать функцию substring вместе с функцией concat в «замещающем значении (XML DML)» .
CREATE TABLE #t(x XML); INSERT INTO #t(x)VALUES(N'<root><id>1</id><timestamp>16-10-2017 19:24:55</timestamp></root>'); UPDATE #t SET x.modify('replace value of (/root/timestamp/text())[1] with concat(substring((/root/timestamp/text())[1],1,2), "/", substring((/root/timestamp/text())[1],4,2), "/", substring((/root/timestamp/text())[1],7) ) ') SELECT*FROM #t;
В результате:
<root><id>1</id><timestamp>16/10/2017 19:24:55</timestamp></root>
Если вам не нужна внешняя необходимость , вы должны использовать строки даты / времени ISO8601 в XML.
Строка dateTime связана с культурой. Чтение этого в разных системах с различными языковыми настройками или настройками даты приведет к ошибкам или – еще хуже !!! – к неправильным результатам.
Датой типа «08-10-2017» может быть 8 октября или 10 августа …
Хуже всего то, что это может успешно пройти все ваши тесты, но будет ломаться на машине клиента со странными сообщениями об ошибках или плохими результатами вплоть до реального повреждения данных!
Переключение дефиса на косые черты просто косметическое! XML – это строго определенный контейнер данных . Любые нестроковые данные должны быть представлены как безопасная конвертируемая строка.
Это то, что вы должны делать:
DECLARE @tbl TABLE(ID INT IDENTITY,YourXML XML); INSERT INTO @tbl VALUES (N'<root> <id>1</id> <timestamp>16-10-2017 19:24:55</timestamp> </root>'); UPDATE @tbl SET YourXml.modify(N'replace value of (/root/timestamp/text())[1] with concat( substring((/root/timestamp/text())[1],7,4), "-" ,substring((/root/timestamp/text())[1],4,2), "-" ,substring((/root/timestamp/text())[1],1,2), "T" ,substring((/root/timestamp/text())[1],12,8) ) cast as xs:dateTime?'); SELECT * FROM @tbl;
Результат
<root> <id>1</id> <timestamp>2017-10-16T19:24:55</timestamp> </root>
вы можете попробовать замену строки, как показано ниже.
update testtable set data= cast( concat( left(cast(data as varchar(max)),charindex('<timestamp>',cast(data as varchar(max)))+len('<timestamp>')-1), replace( substring( cast(data as varchar(max)), len('<timestamp>') + charindex( '<timestamp>', cast(data as varchar(max))) , charindex('</timestamp>',cast(data as varchar(max))) -charindex('<timestamp>',cast(data as varchar(max))) -len('<timestamp>') ), '-','/'), right(cast(data as varchar(max)),len(cast(data as varchar(max)))-charindex('</timestamp>',cast(data as varchar(max)))+1) ) as xml) select * from testtable
рабочая демонстрация