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

Откуда:
Сообщений: 165
Привет,
нужно распарсить XML такого вида:
+
<CDC>
  <CDCInstance>
    <InstanceName>dbo_customer</InstanceName>
    <rows>
      <row>
        <start_lsn>AADMyAAAG8oAFQ==</start_lsn>
        <seqval>AADMyAAAG8oAFA==</seqval>
        <operationid>2</operationid>
        <name>abc company</name>
        <state>md</state>
      </row>
      <row>
        <start_lsn>AADMyAAAG/IABA==</start_lsn>
        <seqval>AADMyAAAG/IAAg==</seqval>
        <operationid>4</operationid>
        <name>abc company</name>
        <state>pa</state>
      </row>
    </rows>
  </CDCInstance>
  <CDCInstance>
    <InstanceName>dbo_customer1</InstanceName>
    <rows>
      <row>
        <start_lsn>AADMyAAAJeQALQ==</start_lsn>
        <seqval>AADMyAAAJeQALA==</seqval>
        <operationid>2</operationid>
        <name>abc company</name>
        <state>md</state>
      </row>
    </rows>
  </CDCInstance>
  <CDCInstance>
    <InstanceName>dbo_customer2</InstanceName>
    <rows>
      <row>
        <start_lsn>AADMyAAAc5QAFQ==</start_lsn>
        <seqval>AADMyAAAc5QAFA==</seqval>
        <operationid>2</operationid>
        <name>xyz company</name>
        <state>de</state>
        <status>st1</status>
      </row>
      <row>
        <start_lsn>AADMyAAAc6oABQ==</start_lsn>
        <seqval>AADMyAAAc6oAAg==</seqval>
        <operationid>1</operationid>
        <name>xyz company</name>
        <state>de</state>
        <status>st1</status>
      </row>
    </rows>
  </CDCInstance>
</CDC>


в таблицу вида:
IDInstanceNamestart_lsnseqvaloperationidnamestatestatus
1dbo_customerAADMyAAAG8oAFQ==AADMyAAAG8oAFA==2abc companymd
2dbo_customerAADMyAAAG/IABA==AADMyAAAG/IAAg==4abc companypa
3dbo_customer1AADMyAAAJeQALQ==AADMyAAAJeQALA==2abc companymd
4dbo_customer2AADMyAAAc5QAFQ==AADMyAAAc5QAFA==2xyz companydest1
5dbo_customer2AADMyAAAc6oABQ==AADMyAAAc6oAAg==1xyz companydest1


проблема заключается в том, что список полей после operationid - неизвестен, и может меняться (как в примере).
Возможно ли так сделать? Если да, то как? Помогите плиз запросом.

Спасибо!

P.S. MS SQL 2008 R2
20 мар 13, 14:51    [14072370]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml с неизвестным числом атрибутов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
whitebeast
Возможно ли так сделать? Если да, то как?
Генерить запрос и выполнять динамическим SQL
20 мар 13, 15:08    [14072499]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml с неизвестным числом атрибутов  [new]
whitebeast
Member

Откуда:
Сообщений: 165
ок, какой запрос должен быть, чтобы парсить хотя бы до поля operationid включительно? у меня никак не получается написать нужный...
20 мар 13, 15:13    [14072527]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml с неизвестным числом атрибутов  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Ммм... зачем динамический SQL?
declare @x xml;

set @x = N'
<CDC>
  <CDCInstance>
    <InstanceName>dbo_customer</InstanceName>
    <rows>
      <row>
        <start_lsn>AADMyAAAG8oAFQ==</start_lsn>
        <seqval>AADMyAAAG8oAFA==</seqval>
        <operationid>2</operationid>
        <name>abc company</name>
        <state>md</state>
      </row>
      <row>
        <start_lsn>AADMyAAAG/IABA==</start_lsn>
        <seqval>AADMyAAAG/IAAg==</seqval>
        <operationid>4</operationid>
        <name>abc company</name>
        <state>pa</state>
      </row>
    </rows>
  </CDCInstance>
  <CDCInstance>
    <InstanceName>dbo_customer1</InstanceName>
    <rows>
      <row>
        <start_lsn>AADMyAAAJeQALQ==</start_lsn>
        <seqval>AADMyAAAJeQALA==</seqval>
        <operationid>2</operationid>
        <name>abc company</name>
        <state>md</state>
      </row>
    </rows>
  </CDCInstance>
  <CDCInstance>
    <InstanceName>dbo_customer2</InstanceName>
    <rows>
      <row>
        <start_lsn>AADMyAAAc5QAFQ==</start_lsn>
        <seqval>AADMyAAAc5QAFA==</seqval>
        <operationid>2</operationid>
        <name>xyz company</name>
        <state>de</state>
        <status>st1</status>
      </row>
      <row>
        <start_lsn>AADMyAAAc6oABQ==</start_lsn>
        <seqval>AADMyAAAc6oAAg==</seqval>
        <operationid>1</operationid>
        <name>xyz company</name>
        <state>de</state>
        <status>st1</status>
      </row>
    </rows>
  </CDCInstance>
</CDC>';

select x.t.value('../../InstanceName[1]', 'nvarchar(1000)') as InstanceName,
       x.t.value('start_lsn[1]', 'nvarchar(1000)')          as start_lsn,
       x.t.value('seqval[1]', 'nvarchar(1000)')             as seqval,
       x.t.value('operationid[1]', 'int')                   as operationid,
       x.t.value('name[1]', 'nvarchar(1000)')               as name,
       x.t.value('state[1]', 'nvarchar(100)')               as state,
       x.t.value('status[1]', 'nvarchar(100)')              as status
from @x.nodes('/CDC/CDCInstance/rows/row') as x(t);
20 мар 13, 15:13    [14072536]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml с неизвестным числом атрибутов  [new]
Гость333
Member

Откуда:
Сообщений: 3683
А, там список полей неизвестен... Т.е. вообще не известен, или возможный набор полей заранее неизвестен, но неизвестно, заполнены ли они?
20 мар 13, 15:15    [14072543]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml с неизвестным числом атрибутов  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Гость333
А, там список полей неизвестен... Т.е. вообще не известен, или возможный набор полей заранее неизвестен, но неизвестно, заполнены ли они?

исправил
20 мар 13, 15:15    [14072548]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml с неизвестным числом атрибутов  [new]
whitebeast
Member

Откуда:
Сообщений: 165
Гость333,
очень хороший вопрос! я не очень правильно сформировал задание.

итак:
список полей заранее известный только такой:
IDInstanceNamestart_lsnseqvaloperationid

дальше может идти абсолютно все, что угодно.
поэтому наверное лучше всего было бы получить таблицу вида:
например вот так:
IDInstanceNamexml
1dbo_customer<rows><row><start_lsn>AADMyAAAG8oAFQ==</start_lsn><seqval>AADMyAAAG8oAFA==</seqval><operationid>2</operationid><name>abc company</name><state>md</state></row><row><start_lsn>AADMyAAAG/IABA==</start_lsn><seqval>AADMyAAAG/IAAg==</seqval><operationid>4</operationid><name>abc company</name><state>pa</state></row></rows>
2dbo_customer1<rows><row><start_lsn>AADMyAAAJeQALQ==</start_lsn><seqval>AADMyAAAJeQALA==</seqval><operationid>2</operationid><name>abc company</name><state>md</state></row></rows>
3dbo_customer2<row><start_lsn>AADMyAAAc5QAFQ==</start_lsn><seqval>AADMyAAAc5QAFA==</seqval><operationid>2</operationid><name>xyz company</name><state>de</state><status>st1</status></row><row><start_lsn>AADMyAAAc6oABQ==</start_lsn><seqval>AADMyAAAc6oAAg==</seqval><operationid>1</operationid><name>xyz company</name><state>de</state><status>st1</status></row></rows>


чтобы потом построчно парсить уже поле XML
20 мар 13, 15:30    [14072641]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml с неизвестным числом атрибутов  [new]
whitebeast
Member

Откуда:
Сообщений: 165
а потом уже допустим распарсить поле XML в нечто подобное:
+
1.
start_lsnseqvaloperationidnamestate
AADMyAAAG8oAFQ==AADMyAAAG8oAFA==2abc companymd
AADMyAAAG/IABA==AADMyAAAG/IAAg== 4abc companypa

2.
start_lsnseqvaloperationidnamestate
AADMyAAAJeQALQ==AADMyAAAJeQALA==2abc companymd

3.
start_lsnseqvaloperationidnamestatestatus
AADMyAAAc5QAFQ==AADMyAAAc5QAFA==2xyz companydest1
AADMyAAAc6oABQ==AADMyAAAc6oAAg==1xyz companydest1

т.е. получить три таблицы - равное количеству InstanceName в исходном XML
20 мар 13, 15:37    [14072693]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml с неизвестным числом атрибутов  [new]
Гость333
Member

Откуда:
Сообщений: 3683
declare @x xml;

set @x = N'
<CDC>
  <CDCInstance>
    <InstanceName>dbo_customer</InstanceName>
    <rows>
      <row>
        <start_lsn>AADMyAAAG8oAFQ==</start_lsn>
        <seqval>AADMyAAAG8oAFA==</seqval>
        <operationid>2</operationid>
        <name>abc company</name>
        <state>md</state>
      </row>
      <row>
        <start_lsn>AADMyAAAG/IABA==</start_lsn>
        <seqval>AADMyAAAG/IAAg==</seqval>
        <operationid>4</operationid>
        <name>abc company</name>
        <state>pa</state>
      </row>
    </rows>
  </CDCInstance>
  <CDCInstance>
    <InstanceName>dbo_customer1</InstanceName>
    <rows>
      <row>
        <start_lsn>AADMyAAAJeQALQ==</start_lsn>
        <seqval>AADMyAAAJeQALA==</seqval>
        <operationid>2</operationid>
        <name>abc company</name>
        <state>md</state>
      </row>
    </rows>
  </CDCInstance>
  <CDCInstance>
    <InstanceName>dbo_customer2</InstanceName>
    <rows>
      <row>
        <start_lsn>AADMyAAAc5QAFQ==</start_lsn>
        <seqval>AADMyAAAc5QAFA==</seqval>
        <operationid>2</operationid>
        <name>xyz company</name>
        <state>de</state>
        <status>st1</status>
      </row>
      <row>
        <start_lsn>AADMyAAAc6oABQ==</start_lsn>
        <seqval>AADMyAAAc6oAAg==</seqval>
        <operationid>1</operationid>
        <name>xyz company</name>
        <state>de</state>
        <status>st1</status>
      </row>
    </rows>
  </CDCInstance>
</CDC>';

declare @sql nvarchar(max);

set @sql = N'
select x.t.value(''../../InstanceName[1]'', ''nvarchar(max)'') as InstanceName,
       x.t.value(''start_lsn[1]'',          ''nvarchar(max)'') as start_lsn,
       x.t.value(''seqval[1]'',             ''nvarchar(max)'') as seqval,
       x.t.value(''operationid[1]'',        ''int'')           as operationid';

with cte as
(   select distinct x.t.value('local-name(.)', 'nvarchar(max)') as NodeName
    from @x.nodes('/CDC/CDCInstance/rows/row/*') as x(t)
)
select @sql = @sql +
(
  select N',
     x.t.value(''' + c.NodeName + N'[1]'', ''nvarchar(max)'') as ' + c.NodeName
  from cte c
  where c.NodeName not in (N'InstanceName', N'start_lsn', N'seqval', N'operationid')
  for xml path(''), type
).value('.', 'nvarchar(max)');

set @sql = @sql + N'
from @x.nodes(''/CDC/CDCInstance/rows/row'') as x(t)';

print @sql;

exec sp_executesql @sql, N'@x xml', @x;


Только объясните, пожалуйста, что вы будете делать с результатом, набор столбцов которого не определён :-)
20 мар 13, 15:52    [14072781]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml с неизвестным числом атрибутов  [new]
whitebeast
Member

Откуда:
Сообщений: 165
Спасибо ) очень очень помогли!

дальше мы будем переносить данные из одной базы в другую. xml - результат переноса данных из CDC таблиц.
таблиц этих ооочень много, и все разные по структуре. можно было бы написать под каждую из них свой обработчик, но это долго.

вы же помогли сделать просто отличный инструмент! еще раз огромное спасибо!
20 мар 13, 16:05    [14072856]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить