Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Есть эксемель, который содержит данные в определенной "неплоской" структуре.
Можно ли гарантировать порядок строк при селекте из данного XML? Если да, то как?

DECLARE
  @MyData xml = '<data>
  <row>
    <col> ООО ВЕКТОР + </col>
    <col />
    <col />
    <col />
  </row>
  <row>
    <col>По договору от 06 06 06</col>
    <col />
    <col />
    <col />
  </row>
  <row>
    <col>Поставщик: ООО Ромашка-М</col>
    <col />
    <col />
    <col />
   </row>
  <row>
    <col>N заказа</col>
    <col>Позиция</col>
    <col>Цена</col>
    <col>Итого</col>
  </row>
  <row>
    <col>1</col>
    <col>Пивная открывашка</col>
    <col>1</col>
    <col />
  </row>
  <row>
    <col />
    <col>Фантик бумажный</col>
    <col>3</col>
    <col />
  </row>
    <row>
    <col />
    <col>Ластик</col>
    <col>2.33</col>
    <col />
  </row>
  <row>
    <col />
    <col />
    <col />
    <col>6.33</col>
  </row>
   <row>
    <col>2</col>
    <col>Трамблёр</col>
    <col>99</col>
    <col />
  </row>
  <row>
    <col />
    <col>Стартёр</col>
    <col>100</col>
    <col />
  </row>
    <row>
    <col />
    <col>Вахтёр</col>
    <col>1</col>
    <col />
  </row>
  <row>
    <col />
    <col />
    <col />
    <col>200.00</col>
  </row>
</data>'
 


SELECT 
  [Col1] =  X.R.value('col[1]','NVarchar (1000)') 
, [Col2] =  X.R.value('col[2]','NVarchar (1000)')
, [Col3] =  X.R.value('col[3]','NVarchar (1000)')
, [Col4] =  X.R.value('col[4]','NVarchar (1000)')
FROM @MyData.nodes('/data/row') X(R)
10 окт 14, 12:54    [16686888]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Порядок гарантирует только ORDER BY и то при SELECT
10 окт 14, 13:22    [16687079]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
может так как то
SELECT 
  [Col1] =  X.R.value('col[1]','NVarchar (1000)') 
, [Col2] =  X.R.value('col[2]','NVarchar (1000)')
, [Col3] =  X.R.value('col[3]','NVarchar (1000)')
, [Col4] =  X.R.value('col[4]','NVarchar (1000)')
FROM @MyData.nodes('/data/row') X(R)
order by X.R.value('count(for $s in /data/row where $s << . return("1"))', 'int')
10 окт 14, 13:27    [16687116]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Если есть таблица натуральных чисел, то можно так:
SELECT 
  [Col1] =  X.R.value('col[1]','NVarchar (1000)') 
, [Col2] =  X.R.value('col[2]','NVarchar (1000)')
, [Col3] =  X.R.value('col[3]','NVarchar (1000)')
, [Col4] =  X.R.value('col[4]','NVarchar (1000)')
FROM
 dbo.Numbers n cross apply
 @MyData.nodes('/data/row[sql:column("n.n")]') X(R)
where
 n.n between 1 and @MyData.value('count(/data/row)', 'int')
order by
 n.n;
10 окт 14, 14:10    [16687433]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
В исходной XML нет полей, за которые можно "зацепится". Есть некая "иерархическая" структура, которую рисует XML и ее нельзя потерять. Я для примера там нарисовал номера заказов и цены, но это частный случай. А меня интересует решение в общем случае.
10 окт 14, 14:18    [16687486]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
invm, а, я так понял, что вы предлагаете джойнить на набор предрасчитанных данных?

Пронумеровать то можно и без такой таблицы. Проблема в том, что откуда я знаю, что "парсер XML" вернет мне строки именно сверху вниз?
DECLARE @A TABLE (i int ) 
INSERT INTO @a SELECT 1 
SELECT 
  [Col1] =  X.R.value('col[1]','NVarchar (1000)') 
, [Col2] =  X.R.value('col[2]','NVarchar (1000)')
, [Col3] =  X.R.value('col[3]','NVarchar (1000)')
, [Col4] =  X.R.value('col[4]','NVarchar (1000)')

, [ZZ] = ROW_NUMBER() OVER (ORDER BY A.i)
FROM @MyData.nodes('/data/row') X(R)
INNER JOIN @A A ON 1= 1 
10 окт 14, 14:21    [16687503]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
Cammomile
В исходной XML нет полей, за которые можно "зацепится". Есть некая "иерархическая" структура, которую рисует XML и ее нельзя потерять. Я для примера там нарисовал номера заказов и цены, но это частный случай. А меня интересует решение в общем случае.

что значит нет полей, за которые можно "зацепится"
чтобы получить некую таблицу вы задаёте путь до узла уровня строк(в данном примере это row)
мы предлагаем вам воспользоваться для сортировки номером по порядку данных узлов внутри документа
10 окт 14, 14:27    [16687534]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
Cammomile
invm, а, я так понял, что вы предлагаете джойнить на набор предрасчитанных данных?

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

для сортировки используется номер узла в документе а не в результирующем рекордсете
SELECT 
  [Col1] =  X.R.value('col[1]','NVarchar (1000)') 
, [Col2] =  X.R.value('col[2]','NVarchar (1000)')
, [Col3] =  X.R.value('col[3]','NVarchar (1000)')
, [Col4] =  X.R.value('col[4]','NVarchar (1000)')
FROM @MyData.nodes('/data/row') X(R)
order by X.R.value('count(for $s in /data/row where $s << . return("1"))', 'int')

SELECT 
  [Col1] =  X.R.value('col[1]','NVarchar (1000)') 
, [Col2] =  X.R.value('col[2]','NVarchar (1000)')
, [Col3] =  X.R.value('col[3]','NVarchar (1000)')
, [Col4] =  X.R.value('col[4]','NVarchar (1000)')
FROM @MyData.nodes('/data/row') X(R)
order by X.R.value('count(for $s in /data/row where $s << . return("1"))', 'int') desc
10 окт 14, 14:30    [16687549]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
order by X.R.value('count(for $s in /data/row where $s << . return("1"))', 'int') desc

Вот эту строку я вообще не понял. Не могли бы вы разъяснить более подробно?
10 окт 14, 14:31    [16687559]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
это колчество узлов от первого до текущего то есть номер узла
SELECT 
  [Col1] =  X.R.value('col[1]','NVarchar (1000)') 
, [Col2] =  X.R.value('col[2]','NVarchar (1000)')
, [Col3] =  X.R.value('col[3]','NVarchar (1000)')
, [Col4] =  X.R.value('col[4]','NVarchar (1000)')
,X.R.value('count(for $s in /data/row where $s << . return("1"))', 'int') 
FROM @MyData.nodes('/data/row') X(R)
order by X.R.value('count(for $s in /data/row where $s << . return("1"))', 'int') 
10 окт 14, 14:35    [16687573]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4900
Cammomile
order by X.R.value('count(for $s in /data/row where $s << . return("1"))', 'int') desc

Вот эту строку я вообще не понял. Не могли бы вы разъяснить более подробно?


The for $i in . clause defines a variable named $i that contains the current node (.). This is basically a hack to work around XQuery's lack of an XSLT-like current() function.
The ../* expression selects all siblings (children of the parent) of the current node.
The [. << $i] predicate filters the list of siblings to those that precede (<<) the current node ($i).
We count() the number of preceding siblings and then add 1 to get the position. That way the first node (which has no preceding siblings) is assigned a position of 1.

http://stackoverflow.com/questions/1134075/finding-node-order-in-xml-document-in-sql-server
10 окт 14, 14:36    [16687576]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Ого, вот это лайфхак! Спасибо коллеги!
10 окт 14, 14:41    [16687605]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Как я вижу, подсчет номера ноды съедает огромную часть ресурсов. Нет ли способа быстрее?
10 окт 14, 14:51    [16687675]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4900
Cammomile
Как я вижу, подсчет номера ноды съедает огромную часть ресурсов. Нет ли способа быстрее?


Пишите CLR функцию -- в .NET гораздо обширней функционал.

Можно применить SSIS.
10 окт 14, 14:52    [16687686]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
Пронумеруйте строки по тексту.
Такая софтина на коленке за 10 минут пишется.

<row>
вставить <col>1</col>
<col> ООО ВЕКТОР + </col>
<col />
<col />
<col />
</row>
10 окт 14, 14:57    [16687726]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4900
Владислав Колосов
Пронумеруйте строки по тексту.
Такая софтина на коленке за 10 минут пишется.



Вопрос где -- в файле не проблема -- в БД смысла нет, лучше dataflow написать в SSIS и там в обработчике перебирать ноды.
<row rn="1">
<col> ООО ВЕКТОР + </col>
<col />
<col />
<col />
</row>


Вопрос в том, что за ситуации. Имеет ли TC возможность менять исходные данные?
10 окт 14, 15:01    [16687751]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
ТСу на вход суют XML, в котором он ничего не меняет. Нужно данные вынуть и пролить в таблицу.

Способ с рассчетом нод хорош, но беда в том, что 2000 нод он нумерует около минуты, в то время как просто селект работает 3-4 секунды. Эта просадка, конечно, огорчает.
10 окт 14, 15:14    [16687832]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4900
Cammomile
ТСу на вход суют XML, в котором он ничего не меняет. Нужно данные вынуть и пролить в таблицу.

Способ с рассчетом нод хорош, но беда в том, что 2000 нод он нумерует около минуты, в то время как просто селект работает 3-4 секунды. Эта просадка, конечно, огорчает.


Надо на .NET делать. Посмотреть скоростные методы прохода по XML XPathNavigator например или потоковое чтение запись. Там вы сможете за доли секунды номера проставить. В общем CLR функция или SSIS -- DataFlow -- Script Component два места, где вы можете задействовать .NET
10 окт 14, 15:29    [16687898]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Ssis отпадает, а вот CLR это да, это вариант.
10 окт 14, 15:30    [16687904]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Так ???
SELECT IDENTITY(integer,1,1) AS mynr,
  [Col1] =  X.R.value('col[1]','NVarchar (1000)') 
, [Col2] =  X.R.value('col[2]','NVarchar (1000)')
, [Col3] =  X.R.value('col[3]','NVarchar (1000)')
, [Col4] =  X.R.value('col[4]','NVarchar (1000)')
into #X
FROM @MyData.nodes('/data/row') X(R)

select * from #x order by mynr
drop table #x
10 окт 14, 15:35    [16687928]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А так вы гарантируете, что
FROM @MyData.nodes('/data/row') X(R)
вернет вам строки ровно в таком порядке как в XML идут ноды?
10 окт 14, 15:42    [16687964]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4900
Glory
Так ???
SELECT IDENTITY(integer,1,1) AS mynr,
  [Col1] =  X.R.value('col[1]','NVarchar (1000)') 
, [Col2] =  X.R.value('col[2]','NVarchar (1000)')
, [Col3] =  X.R.value('col[3]','NVarchar (1000)')
, [Col4] =  X.R.value('col[4]','NVarchar (1000)')
into #X
FROM @MyData.nodes('/data/row') X(R)

select * from #x order by mynr
drop table #x


А есть ли гарантия, что SELECt из @MyData.nodes('/data/row') X(R) будет данные выбирать в строго определённом порядке? В этом я так понимаю главный вопрос. Стандартом это не гарантировано. При переходе на новую версию SQL всё может поехать без предупреждения.
10 окт 14, 15:43    [16687970]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
" В этом я так понимаю главный вопрос. "

Истинно так!
10 окт 14, 15:46    [16687984]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Glory
Member

Откуда:
Сообщений: 104751
a_voronin
А есть ли гарантия, что SELECt из @MyData.nodes('/data/row') X(R) будет данные выбирать в строго определённом порядке?

В строго определенном - это не начнет ли он парсит xml с конца ?
10 окт 14, 15:47    [16687991]     Ответить | Цитировать Сообщить модератору
 Re: Как гарантировать порядок строк при селекте из XML?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Возьмите edge формат тогда
DECLARE
  @MyData nvarchar(max) = N'<data>
  <row>
    <col> ООО ВЕКТОР + </col>
    <col />
    <col />
    <col />
  </row>
  <row>
    <col>По договору от 06 06 06</col>
    <col />
    <col />
    <col />
  </row>
  <row>
    <col>Поставщик: ООО Ромашка-М</col>
    <col />
    <col />
    <col />
   </row>
  <row>
    <col>N заказа</col>
    <col>Позиция</col>
    <col>Цена</col>
    <col>Итого</col>
  </row>
  <row>
    <col>1</col>
    <col>Пивная открывашка</col>
    <col>1</col>
    <col />
  </row>
  <row>
    <col />
    <col>Фантик бумажный</col>
    <col>3</col>
    <col />
  </row>
    <row>
    <col />
    <col>Ластик</col>
    <col>2.33</col>
    <col />
  </row>
  <row>
    <col />
    <col />
    <col />
    <col>6.33</col>
  </row>
   <row>
    <col>2</col>
    <col>Трамблёр</col>
    <col>99</col>
    <col />
  </row>
  <row>
    <col />
    <col>Стартёр</col>
    <col>100</col>
    <col />
  </row>
    <row>
    <col />
    <col>Вахтёр</col>
    <col>1</col>
    <col />
  </row>
  <row>
    <col />
    <col />
    <col />
    <col>200.00</col>
  </row>
</data>'

DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @MyData
SELECT * FROM OPENXML(@docHandle, N'/data/row') 
EXEC sp_xml_removedocument @docHandle 
10 окт 14, 15:50    [16688007]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить