XQuery Получить позицию ()

Это часть нашего xml-файла.

                <point distanceTotal="162" seqNo="189">
                <lineSection id="395" track="1" direction="1">
                <outInfos>
                    <comment commentTypeId="4" priority="1"oneLiner="BOT">
                        <layerVPK seasonValue="S0"/>
                        <vectors>
                            <vector dateFrom="2016-12-11"/>
                        </vectors>
                        <frenchText>1x3 MH</frenchText>
                    </comment>
                    <comment commentTypeId="4" priority="1" oneLiner="bot">
                        <layerVPK seasonValue="S0"/>
                        <frenchText>Réception voie occupée</frenchText>
                        <dutchText>Test</dutchText>
                    </comment>
                </outInfos>
            </point>

Мы загружаем это в столбец SqlServer и с помощью XQuery получаем значения. Но я не могу найти способ закодировать position(), и в основном T-SQL ROW_NUMBER или плотный ранг нельзя использовать, поскольку не всегда все данные существуют. Например, голландский текст существует только во втором комментарии, и нет поля, которое идентифицирует 2 комментария....

Это код SQL

SELECT  fi.file_uid,
        fi.file_date,
        T1.ref.value('@id',           'varchar(100)') AS gTV_id,
        T2.ref.value('@id',           'varchar(100)') AS gTrn_id,
        T4.ref.value('@seqNo',        'varchar(100)') AS gTrnTPp_seqNo,
        T7.ref.value('text()[1]',     'varchar(1000)') AS gTrnTPpOiCDT_Text,
        T6.ref.query('/globalTrainVariant/trains/globalTrainVariant/train/timetablePoints/point/outInfos/comment[position()]') AS Test
   FROM ods.filesin fi
        CROSS APPLY fi.file_xml.nodes('declare namespace cern="http://...";
                                       (/cern:trains/globalTrainVariant)') T1(ref)
        CROSS APPLY T1.ref.nodes('declare namespace cern="http://...";
                                  (train)') T2(ref)
        CROSS APPLY T2.ref.nodes('declare namespace cern="http://...";
                                 (timetablePoints)') T3(ref)
        CROSS APPLY T3.ref.nodes('declare namespace cern="http://...";
                                  (point)') T4(ref)
        CROSS APPLY T4.ref.nodes('declare namespace cern="http://...";
                                  (outInfos)') T5(ref)
        CROSS APPLY T5.ref.nodes('declare namespace cern="http://...";
                                  (comment)') T6(ref)
        CROSS APPLY T6.ref.nodes('declare namespace cern="http://...";
                                  (dutchText)') T7(ref)
  WHERE fi.file_type = 'trains'

Код ошибок не выдает, но поле Test всегда пустое.

Какие-либо предложения ?


sql-server xquery-sql
person Harry Leboeuf    schedule 09.10.2017    source источник
comment
Я не думаю, что функция положения работает так. Также это, очевидно, не вернет позицию правильно, потому что вы пытаетесь использовать путь от T6. Что может сработать для вас, так это подзапрос с row_number() и внешнее применение вместо перекрестного применения для T7(ref), чтобы получить правильный номер строки, а затем исключить те, где t7.ref имеет значение null   -  person ZLK    schedule 10.10.2017

Ответы (3)


arrow_upward
2
arrow_downward

Если вы посмотрите документацию, вы увидите, что на данный момент вы не можете напрямую вернуть результат функции position():

В SQL Server fn:position() можно использовать только в контексте контекстно-зависимого предиката. В частности, его можно использовать только внутри квадратных скобок ([ ]).

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

declare @x xml = N'<point distanceTotal="162" seqNo="189">
  <outInfos>
    <comment commentTypeId="4" priority="1" oneLiner="BOT">
      <layerVPK seasonValue="S0" />
      <vectors>
        <vector dateFrom="2016-12-11" />
      </vectors>
      <frenchText>1x3 MH</frenchText>
    </comment>
    <comment commentTypeId="4" priority="1" oneLiner="bot">
      <layerVPK seasonValue="S0" />
      <frenchText>Réception voie occupée</frenchText>
      <dutchText>Test</dutchText>
    </comment>
  </outInfos>
</point>';

with cte as (
    select top (1000) row_number() over(order by ac.object_id) as [RN]
    from sys.all_columns ac
)
select t.c.query('.') as [OutInfos], sq.RN as [TextPosition], x.c.query('.') as [DutchComment]
from @x.nodes('/point/outInfos') t(c)
    cross join cte sq
    cross apply t.c.nodes('./comment[position() = sql:column("sq.RN")]/dutchText') x(c);

В нем CTE создает упорядоченный набор целых чисел (обычно я держу рядом специальную таблицу, но вы всегда можете создать ее по ходу дела), а условие совпадения указывается в выражении XQuery, определяющем вывод x(c).

person Roger Wolf    schedule 10.10.2017
comment
Для тех, кто ищет что-то похожее, вот последний запрос... - person Harry Leboeuf; 10.10.2017
comment
SELECT ... T7.ref.value('text()[1]', 'varchar(1000)') AS gTrnTPpOiCDT_Text, xRows1.RN AS gTrnTPpOiC_seqNo, xRows2.RN AS gTrnTPpOiCDT_seqNo ... CROSS APPLY ods.RowNumbers_5 xRows1 CROSS ПРИМЕНИТЬ T5.ref.nodes('dec...;(./comment[position() = sql:column(xRows1.RN)])') T6(ref) ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ ods.RowNumbers_1 xRows2 ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ T6.ref. nodes('dec...;(./dutchText[position() = sql:column(xRows2.RN)])') T7(ref) WHERE fi.file_type = 'trains' - person Harry Leboeuf; 10.10.2017
comment
Пришлось обрезать некоторые части до предела длины комментария. Но с исходным запросом в сторону можно будет составить. - person Harry Leboeuf; 10.10.2017

arrow_upward
2
arrow_downward

Я согласен с Роджером, что функция position() не может быть вызвана напрямую и должна быть внутри []. Однако есть решение, которое не требует дополнительных таблиц и поддерживает любое количество строк с помощью рекурсии:

    declare @Xml xml = N'<?xml version="1.0" encoding="utf-16"?>
<root>
    <n>1</n>
    <n>10</n>
    <n>5</n>
    <n>3</n>
    <n>11</n>
</root>';

with cte as
(
    select t.c.value(N'n[1]', N'int') n, 1 RowNum
    from @Xml.nodes(N'root[1]') t(c)
    where t.c.exist(N'n[1]') = 1
    union all
    select t.c.value(N'n[position() = sql:column("cte.RowNum") + 1][1]', N'int') n, cte.RowNum + 1
    from @Xml.nodes(N'root[1]') t(c)
    cross join cte
    where t.c.exist(N'n[position() = sql:column("cte.RowNum") + 1]') = 1
)
select *
from cte;
person Vasily Ivanov    schedule 20.10.2017

arrow_upward
1
arrow_downward

Возможно, было бы проще и эффективнее использовать Операторы сравнения порядка узлов для подсчета предшествующих //узлов комментариев в дереве XML.

Я не тестировал огромные XML-документы, но это определенно менее интенсивное ввод-вывод и менее интенсивное использование ЦП в моих надуманных тестах.

declare @x xml = N'<point distanceTotal="162" seqNo="189">
  <outInfos>
    <comment commentTypeId="4" priority="1" oneLiner="BOT">
      <layerVPK seasonValue="S0" />
      <vectors>
        <vector dateFrom="2016-12-11" />
      </vectors>
      <frenchText>1x3 MH</frenchText>
    </comment>
    <comment commentTypeId="4" priority="1" oneLiner="bot">
      <layerVPK seasonValue="S0" />
      <frenchText>Réception voie occupée</frenchText>
      <dutchText>Test</dutchText>
    </comment>
  </outInfos>
</point>';

select
    [OutInfos] = t.c.query('../..'),
    [TextPosition] = t.c.value('let $dutchText := . return count(../../comment[. << $dutchText])', 'int'),
    [DutchComment] = t.c.query('.')
from @x.nodes('/point/outInfos/comment/dutchText') t(c)
person user9361177    schedule 14.02.2018