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

Откуда: Краснодар
Сообщений: 1484
Уважаемые пользователи,

Помогите понять как мне вытащить из поля типа xml значения некоторых полей. У меня есть таблица, в которой хранятся отчеты ОЛАП (основанные на OWC) в виде XML поля. Я пытаюсь вытянуть из строки (это только одна строка таблицы, формат таблицы:
[reportname] varchar (300), [XML] text
)
значения атрибутов x:SourceName gри условии, что атрибут x:Orientation не пуст.

вот скрипт, который я написал по аналогии с тем, что нашел на форуме:
DECLARE @X XML = '<xml xmlns:x="urn:schemas-microsoft-com:office:excel">
  <x:PivotTable>
    <x:OWCVersion>10.0.0.4109         </x:OWCVersion>
    <x:DisplayScreenTips />
    <x:NoAutoFit />
    <x:Height>674</x:Height>
    <x:Width>1128</x:Width>
    <x:CubeProvider>MSOLAP.3</x:CubeProvider>
    <x:DisplayFieldList />
    <x:FieldListTop>632</x:FieldListTop>
    <x:FieldListLeft>574</x:FieldListLeft>
    <x:FieldListBottom>1062</x:FieldListBottom>
    <x:FieldListRight>776</x:FieldListRight>
    <x:CacheDetails />
    <x:Name>Microsoft Office PivotTable 10.0</x:Name>
    <x:PivotField>
      <x:Name>Brend</x:Name>
      <x:SourceName>[Brend].[Brend]</x:SourceName>
      <x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
    </x:PivotField>
    <x:PivotField>
      <x:Name>Machine Format</x:Name>
      <x:SourceName>[Machine Format].[Machine Format]</x:SourceName>
      <x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
    </x:PivotField>
    <x:PivotField>
      <x:Name>Axe</x:Name>
      <x:SourceName>[Axe].[Axe]</x:SourceName>
      <x:Orientation>Row</x:Orientation>
      <x:Position>3</x:Position>
      <x:NumberFormat>0.0</x:NumberFormat>
      <x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
    </x:PivotField>
  </x:PivotTable>
</xml>'

select sysdatetime() as dt, @X as X
into tmp_tbl

select
      t.c.value('./SourceName[1]', 'nvarchar(1000)')
	 ,t.c.value('./Orientation[1]', 'nvarchar(1000)')	 
from tmp_tbl 
cross apply X.nodes('(xml/PivotTable/PivotField)') as t(c)
drop table tmp_tbl;


Ничего не получается. Предположу, что виноват префикс x:, потому и заменяю его:

cast(replace(cast([@X] as varchar(max)),'x:','') as XML)


Вроде что-то появляется. А вот как мне зацепить полученный результат с самой таблицей.
Ведь то, что я вытащил - лишь одна строка оттуда, но как мне имя отчета reportname из другого поля вывести рядом со всем этим парсингом?
20 фев 12, 19:24    [12125979]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
iljy
Member

Откуда:
Сообщений: 8711
Grigoriy,
;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:excel' as x)
select
      t.c.value('./x:SourceName[1]', 'nvarchar(1000)')
	 ,t.c.value('./x:Orientation[1]', 'nvarchar(1000)')	 
from tmp_tbl 
cross apply X.nodes('(xml/x:PivotTable/x:PivotField)[x:Orientation]') as t(c)
20 фев 12, 19:48    [12126088]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
Спасибо, помогло. А как мне состыковать это с другими полями таблицы? с полем reportname?
21 фев 12, 09:40    [12127604]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
iljy
Member

Откуда:
Сообщений: 8711
Grigoriy
Спасибо, помогло. А как мне состыковать это с другими полями таблицы? с полем reportname?

А что в вашем понимании означает термин "состыковать" применительно к реляционным БД, и что за магическое поле reportname?
21 фев 12, 09:44    [12127624]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
Как я писал выше, в заглавном сообщении, есть таблица в реляционной СУБД (далее - просто таблица), которая состоит из двух полей:
[reportname] varchar (300), [XML] text

Одно из полей таблицы, называемое XML, я, с вашей помощью, превратил в выборку вида:
DimensionName Orientation
[Time].[Year] Column
[Time].[Month] Column
[Time].[Day] Column
[Time by week].[Year] Page

Но т.к. в таблице есть еще поле [reportname], то я бы хотел видеть и его в выборке.


ReportName DimensionName Orientation
Axis [Time].[Year] Column
Axis [Time].[Month] Column
Axis [Time].[Day] Column
Locke [Time by week].[Year] Page
21 фев 12, 10:01    [12127763]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
iljy
Member

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

ну так и впишите его в список select, оно в той же самой таблице, что мешает-то??
21 фев 12, 10:19    [12127892]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
iljy,

спасибо, появилось поле. Но на каждый репорт всего одна строка, вместо распарсенных 20:
;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:excel' as x)
select [Name],
      t.c.value('x:DataMember[1]', 'nvarchar(1000)')      as [cube] ,
      t.c.value('./x:PivotField[1]/x:Name[1]', 'nvarchar(1000)') Attr     
      ,t.c.value('./x:PivotField[1]/x:SourceName[1]', 'nvarchar(1000)') Dim
	 ,t.c.value('./x:PivotField[1]/x:Orientation[1]', 'nvarchar(1000)')	Orient
from tmp_tbl 
cross apply X.nodes('(xml/x:PivotTable)') as t(c)
where t.c.value('./x:PivotField[1]/x:Orientation[1]', 'nvarchar(1000)') is not null
order by 1,2

Понимаю, что для SourceName нельзя ставить [1], но как правильно - не могу понять
21 фев 12, 18:16    [12132472]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
iljy
Member

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

? А как должно быть? В вашем примере XML один узел xml/x:PivotTable, естественно nodes возвращает одну строку, что не так?
21 фев 12, 18:26    [12132507]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
iljy,

Ну я хотел, чтобы возвращались все x:PivotField для этого узла
21 фев 12, 19:21    [12132789]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
iljy
Member

Откуда:
Сообщений: 8711
Grigoriy
iljy,

Ну я хотел, чтобы возвращались все x:PivotField для этого узла

Тогда зачем вы переделали мой вариант? Он делал именно это
21 фев 12, 19:34    [12132842]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
Ваш вариант вытаскивает значеня параметров SourceName, Orientation для каждой ветки PivotField
Но за пределами этих веток есть общий параметр
<x:Name>Microsoft Office PivotTable 10.0</x:Name>
Вот его таким запросом я вытащить не смог
21 фев 12, 19:46    [12132905]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
iljy
Member

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

читайте про XPath, иначе так и будете вслепую гадать. А пока - вот два варианта:
;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:excel' as x)
select
      t.c.value('./x:SourceName[1]', 'nvarchar(1000)')
	 ,t.c.value('./x:Orientation[1]', 'nvarchar(1000)')
	 ,t.c.value('../x:Name[1]', 'nvarchar(1000)')
from tmp_tbl 
cross apply X.nodes('(xml/x:PivotTable/x:PivotField)[x:Orientation]') as t(c)

;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:excel' as x)
select t.*, tt.c.value('./x:Name[1]', 'nvarchar(1000)')
from tmp_tbl 
cross apply X.nodes('xml/x:PivotTable') as tt(c)
cross apply
(
	select t.c.value('./x:SourceName[1]', 'nvarchar(1000)') SName, t.c.value('./x:Orientation[1]', 'nvarchar(1000)') Orientation
	from tt.c.nodes('x:PivotField[x:Orientation]') as t(c)
)t

1й проще, 2й быстрее.
21 фев 12, 20:00    [12132963]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
Спасибо! Я не знал, что надо ставить 2 точки, методом подбора действовал.
Скажите, а реально ли сделать апдейт по тому же принципу? Т.е. заменить значение параметра
там где
Axis [Time].[Day] Column

сделать
Axis [Date].[Day] Row

?

или проще работать как с текстом?
21 фев 12, 20:08    [12132999]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
iljy
Member

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

в принципе можно, но с ограничениями. Читайте про метод modify. Хотя вообще скуль не особо заточен под работу с хмулем, так что для начала подумайте, а надо ли.
21 фев 12, 20:24    [12133074]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
iljy,

Необходимость есть. Но я могу попробовать и replace, раз нет простого метода хмл, в котором я и так ничего не понимаю
21 фев 12, 20:46    [12133170]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
iljy
Member

Откуда:
Сообщений: 8711
Grigoriy
iljy,

Необходимость есть. Но я могу попробовать и replace, раз нет простого метода хмл, в котором я и так ничего не понимаю

Методы есть, но с ограничениями. Например
set @X.modify('
	declare namespace x = "urn:schemas-microsoft-com:office:excel";
	replace value of (xml/x:PivotTable/x:PivotField/x:Orientation[text() = "Row"]/text())[1]
	with     "new text"
')

select @X

Но такой метод работает только с первым вхождением. Можете поизвращаться, но повторюсь: SQL не предназначен для обработки XML. Хотя некоторые возможности присутствуют.
21 фев 12, 21:00    [12133232]     Ответить | Цитировать Сообщить модератору
 Re: парсинг нестандартного XML. Вопрос новичка.  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
Спасибо, вы мне очень помогли
21 фев 12, 21:01    [12133234]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить