MSSQL Парсинг XML и прербразование дат
От: .alex Ниоткуда  
Дата: 02.04.23 09:36
Оценка:
Добрый день. Разбираю xml sql сервером. В xml есть список нодов с датам и значениями. Мне нужно выбрать ноду с максимальной датой.
<root>
    <sub_root>>
        <node>
            <val>1</val>
            <date>31.07.2022</date>
        </node>
        <node>
            <val>2</val>
            <date>20.08.2022</date>
        </node>
    </sub_root>
</root>

Даты в русском/немецком формате!!!
поэтому следующий вариант работает неправильно!
declare @sCmd nvarchar(max), @sFullPath varchar(max), @x xml

set @sFullPath = 'c:\DiskD\ex.xml '
set @sCmd = N'select @xml = cast(t.data as xml) from OPENROWSET (BULK '+quotename(@sFullPath, N'''') + N', SINGLE_BLOB) t(data)'
exec sp_executesql @sCmd, N'@xml xml output', @x output;

select
    ns.n.value('val[1]', 'varchar(16)') 'val'
    , ns.n.value('date[1]', 'varchar(16)') 'date'
from @x.nodes('root/sub_root') sr(n)
    outer apply sr.n.nodes('node[not(../node/date > date)]') ns(n)

Как я понимаю, что он не понимает что это дата и сортирует даты как строки и соответсвенно 31.07.2022 получается больше, чем 20.08.2022
Собственно вопрос можно ли преобразовать даты, чтобы рабтала сортировка?
Re: MSSQL Парсинг XML и прербразование дат
От: Слава  
Дата: 02.04.23 10:45
Оценка:
Здравствуйте, .alex, Вы писали:

A>Добрый день. Разбираю xml sql сервером. В xml есть список нодов с датам и значениями. Мне нужно выбрать ноду с максимальной датой.
A>Собственно вопрос можно ли преобразовать даты, чтобы рабтала сортировка?

Я в вашем коде не вижу ни одного CAST, CONVERT, не вижу указания формата даты. Наверное нужно это добавить.

А лучше для программирования взять язык программирования. T-SQL — это для чтобы помучиться.
Re[2]: MSSQL Парсинг XML и прербразование дат
От: .alex Ниоткуда  
Дата: 02.04.23 11:41
Оценка:
Здравствуйте, Слава, Вы писали:

С>Здравствуйте, .alex, Вы писали:

A>>Добрый день. Разбираю xml sql сервером. В xml есть список нодов с датам и значениями. Мне нужно выбрать ноду с максимальной датой.
A>>Собственно вопрос можно ли преобразовать даты, чтобы рабтала сортировка?

С>Я в вашем коде не вижу ни одного CAST, CONVERT, не вижу указания формата даты. Наверное нужно это добавить.

С>А лучше для программирования взять язык программирования. T-SQL — это для чтобы помучиться.
так куда cast или convert вставлять?
сравнение дат происходит в xpath запросе:
outer apply sr.n.nodes('node[not(../node/date > date)]') ns(n)
т.е. сюда нужно что-то вставить и скорее всего не sql, а что-то из языка xpath/xquery а вот что я не знаю...
Re: MSSQL Парсинг XML и прербразование дат
От: bnk СССР http://unmanagedvisio.com/
Дата: 02.04.23 11:48
Оценка: 76 (1)
Здравствуйте, .alex, Вы писали:

A>Добрый день. Разбираю xml sql сервером. В xml есть список нодов с датам и значениями. Мне нужно выбрать ноду с максимальной датой.
A>Даты в русском/немецком формате!!!

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

DECLARE @xml XML = N'<root>
                        <sub_root>
                            <node>
                                <val>1</val>
                                <date>31.07.2022</date>
                            </node>
                            <node>
                                <val>2</val>
                                <date>20.08.2022</date>
                            </node>
                        </sub_root>
                    </root>';

WITH cte AS (
    SELECT 
        n.value('(val/text())[1]', 'int') AS val,
        CONVERT(date, n.value('(date/text())[1]', 'varchar(10)'), 104) AS date
    FROM @xml.nodes('/root/sub_root/node') AS t(n)
)
SELECT val, date
FROM cte
WHERE date = (SELECT MAX(date) FROM cte)

Здесь мы используем переменную @xml, которая хранит ваш XML-документ.
Затем мы используем CTE для извлечения значений из XML-документа,
преобразуя дату из русского/немецкого формата (день.месяц.год) с использованием кода 104.
Наконец, мы выбираем ноду с максимальной датой из CTE


Код выше он выдал прямо по твоему вопросу.
Про "код 104" для преобразования даты из русского/немецкого я бы ни в жизнь не догадался.
Отредактировано 02.04.2023 11:51 bnk . Предыдущая версия . Еще …
Отредактировано 02.04.2023 11:50 bnk . Предыдущая версия .
Отредактировано 02.04.2023 11:49 bnk . Предыдущая версия .
Re[2]: MSSQL Парсинг XML и прербразование дат
От: Слава  
Дата: 02.04.23 13:33
Оценка: -1
Здравствуйте, bnk, Вы писали:

bnk>Про "код 104" для преобразования даты из русского/немецкого я бы ни в жизнь не догадался.

Я считаю себя посредственным программистом, но про коды культур для CONVERT знаю, во всяком случае про их существование. И 104 выдаётся гуглом второй же ссылкой.
Re[3]: MSSQL Парсинг XML и прербразование дат
От: bnk СССР http://unmanagedvisio.com/
Дата: 02.04.23 15:52
Оценка:
Здравствуйте, Слава, Вы писали:

bnk>>Про "код 104" для преобразования даты из русского/немецкого я бы ни в жизнь не догадался.

С>Я считаю себя посредственным программистом, но про коды культур для CONVERT знаю, во всяком случае про их существование. И 104 выдаётся гуглом второй же ссылкой.

Возможно ты много работал с SQL, в отличие от меня например.
Ну т.е. мне чтобы что-то похожее написать точно пришлось бы попотеть.

Вписался тут на список ожидания на Copilot X, возможно оно вообще за меня ВСЁ делать будет
Re[2]: MSSQL Парсинг XML и прербразование дат
От: .alex Ниоткуда  
Дата: 02.04.23 15:58
Оценка:
Здравствуйте, bnk, Вы писали:


bnk>Код выше он выдал прямо по твоему вопросу.
bnk>Про "код 104" для преобразования даты из русского/немецкого я бы ни в жизнь не догадался.

Спасибо, но использовать cte или временые таблицы не хотелось бы... Я привел пример xml, на самом деле оригинальный файл очень "развесистый" и доставать таким образом нужно будет не одну ноду, а несколько и их очень много, следовательно декартово произведение (outer apply) будет огромным. Хотелось бы чтобы это все делелось на уровне xpath запроса... С форматом дат yyyy-mm-dd все работает "из коробки", чтоже сделать для нашего формата?
Re[3]: MSSQL Парсинг XML и прербразование дат
От: _ABC_  
Дата: 02.04.23 21:22
Оценка:
Здравствуйте, .alex, Вы писали:

A>С форматом дат yyyy-mm-dd все работает "из коробки", чтоже сделать для нашего формата?
Это потому, что этот формат и как строка правильно сортируется.
Вообще, MS SQL — это не про xml. Парсинг XML был добавлен как костыль, чтобы хоть как-то справляться с неструктурированными данными. Не стоит ожидать удобной работы с неструктурированными данными посредством языка и инструмента, заточенных под работу со структурированными. Они имплементировали какую-то версию XQuery для работы с XML, но, ИМХО, это изврат (как и весь XML, собственно).

Вариантов несколько:
1. Работать с ISO стандартом дат, который сортируется ожидаемым образом и как строка тоже и не имеет проблем с переводом в тип date/datetime. ЕМНИП, это даже какая-то из рекомендаций по XML в целом, без всякого отношения к MS SQL Server.
2. Перевести данные в структурированный формат и работать с этим атрибутом как с датой, а не строкой. Да, это, наверное, потребует CTE, временных таблиц и прочего.
3. Я не специалист по XQuery, возможно есть какая-то возможность на этом уровне конвертировать. Факт в том, что ты работаешь с этим атрибутом как со строкой: ns.n.value('date[1]', 'varchar(16)'). Тебе нужна дата.
"Потерял дар речи за зря"(с).
Re[3]: MSSQL Парсинг XML и прербразование дат
От: Sinclair Россия https://github.com/evilguest/
Дата: 12.04.23 16:30
Оценка:
Здравствуйте, .alex, Вы писали:
A>Спасибо, но использовать cte или временые таблицы не хотелось бы...
А чем плохо с СTE?
A>Я привел пример xml, на самом деле оригинальный файл очень "развесистый" и доставать таким образом нужно будет не одну ноду, а несколько и их очень много, следовательно декартово произведение (outer apply) будет огромным.
В коде нет outer apply.
A>Хотелось бы чтобы это все делелось на уровне xpath запроса... С форматом дат yyyy-mm-dd все работает "из коробки", чтоже сделать для нашего формата?
В SQL-серверном XPath нет типа данных "дата". Единственное, что вы можете попытаться сделать — это рукопашную конверсию порядка компонент при помощи комбинации функций fn:substring и fn:concat.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[4]: MSSQL Парсинг XML и прербразование дат
От: .alex Ниоткуда  
Дата: 12.04.23 18:43
Оценка:
Здравствуйте, Sinclair, Вы писали:

S>А чем плохо с СTE?
А потому-что tempdb пухнет, как не в себя....
S>В коде нет outer apply.
Это был примерный xml с одним outer apply, в реальном их много...
S>В SQL-серверном XPath нет типа данных "дата". Единственное, что вы можете попытаться сделать — это рукопашную конверсию порядка компонент при помощи комбинации функций fn:substring и fn:concat. 
DECLARE @xml XML =
'
<root>
    <agree>
        <id>agree_id1</id>
        <node>
            <val>1.1</val>
            <date>31.07.2022</date>
        </node>
        <node>
            <val>1.2</val>
            <date>20.08.2022</date>
        </node>

        <node2>
            <val>1.3</val>
            <date>31.10.2022</date>
        </node2>
        <node2>
            <val>1.4</val>
            <date>20.11.2022</date>
        </node2>
    </agree>

    <agree>
        <id>agree_id2</id>
        <node>
            <val>2.1</val>
            <date>31.05.2022</date>
        </node>
        <node>
            <val>2.2</val>
            <date>20.06.2022</date>
        </node>

        <node2>
            <val>2.3</val>
            <date>31.09.2022</date>
        </node2>
        <node2>
            <val>2.4/</val>
            <date>20.10.2022</date>
        </node2>
    </agree>

</root>
';


SELECT 
    a.n.value('id[1]', 'varchar(max)') 'id'
    , n1.n.value('val[1]', 'varchar(max)') 'n1_val'
    , n1.n.value('date[1]', 'varchar(max)') 'n1_date'
    , n2.n.value('val[1]', 'varchar(max)') 'n2_val'
    , n2.n.value('date[1]', 'varchar(max)') 'n2_date'
FROM @xml.nodes('root/agree') a(n)
    outer apply a.n.nodes('node[not(../node/date > date)]') n1(n)
    outer apply a.n.nodes('node2[not(../node2/date > date)]') n2(n)

а вот в таком примере не подскажете, как эти функции использовать? я пробую просто для примера вставить fn:substring() просто проверитьработает или нет
outer apply a.n.nodes('node[not(../node/date > fn:substring(date, 0, 2))]') n1(n)

выдает ошибку:

XQuery [a.n.nodes()]: "substring()" требует одноэлементного множества (или пустой последовательности), обнаружен операнд типа "xdt:untypedAtomic *"

Re[5]: MSSQL Парсинг XML и прербразование дат
От: Sinclair Россия https://github.com/evilguest/
Дата: 13.04.23 05:44
Оценка:
Здравствуйте, .alex, Вы писали:
A>а вот в таком примере не подскажете, как эти функции использовать? я пробую просто для примера вставить fn:substring() просто проверитьработает или нет
A>
A>outer apply a.n.nodes('node[not(../node/date > fn:substring(date, 0, 2))]') n1(n)
A>

A>выдает ошибку:
A>

A>XQuery [a.n.nodes()]: "substring()" требует одноэлементного множества (или пустой последовательности), обнаружен операнд типа "xdt:untypedAtomic *"

 Попробуйте 
...
outer apply a.n.nodes('node[not(../node/date > fn:substring(fn:string(date), 0, 2))]') n1(n)
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[6]: MSSQL Парсинг XML и прербразование дат
От: .alex Ниоткуда  
Дата: 13.04.23 07:45
Оценка:
Здравствуйте, Sinclair, Вы писали:

S>Попробуйте
S>
S>...
S>outer apply a.n.nodes('node[not(../node/date > fn:substring(fn:string(date), 0, 2))]') n1(n)
S>

XQuery [a.n.nodes()]: "string()" требует одноэлементного множества (или пустой последовательности), обнаружен операнд типа "element(date,xdt:untyped) *"
даже не знаю как это победить... видимо нужно парсить не в сервере...
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.