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

Откуда: Minsk, Belarus
Сообщений: 3
Есть XML следующего вида:

<AuditMsg>
<SourceServer>W2003SQL2005\SQL2008</SourceServer>
<SourceDb>vpiCaptureCollections</SourceDb>
<SourceTable>vpCollectionMembers</SourceTable>
<DMLType>D</DMLType>
<ChangedData>
<t>
<collectionID>5F6CF33A-B8BD-4903-A908-B1EB52A6F1F7</collectionID>
<objectID>7852F12D-E854-4ECB-885B-25AD310A2427</objectID>
<resourceTypeID>386E81D6-901F-425F-B520-3F663716D861</resourceTypeID>
<dateAdded>2010-03-31T10:34:30</dateAdded>
</t>
<t>
<collectionID>5F6CF33A-B8BD-4903-A908-B1EB52A6F1F7</collectionID>
<objectID>EAE17E5F-AF89-4A35-9A3E-A182C644076C</objectID>
<resourceTypeID>386E81D6-901F-425F-B520-3F663716D861</resourceTypeID>
<dateAdded>2010-03-30T15:56:08</dateAdded>
</t>
</ChangedData>
</AuditMsg>'

Необходимо выбрать запросом данные в теге <t> - для каждого тега строка вида

collectionID objectID resourceTypeID dateAdded
5 апр 11, 11:17    [10472086]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
declare @xml xml

set @xml = '<AuditMsg>
<SourceServer>W2003SQL2005\SQL2008</SourceServer>
<SourceDb>vpiCaptureCollections</SourceDb>
<SourceTable>vpCollectionMembers</SourceTable>
<DMLType>D</DMLType>
<ChangedData>
<t>
<collectionID>5F6CF33A-B8BD-4903-A908-B1EB52A6F1F7</collectionID>
<objectID>7852F12D-E854-4ECB-885B-25AD310A2427</objectID>
<resourceTypeID>386E81D6-901F-425F-B520-3F663716D861</resourceTypeID>
<dateAdded>2010-03-31T10:34:30</dateAdded>
</t>
<t>
<collectionID>5F6CF33A-B8BD-4903-A908-B1EB52A6F1F7</collectionID>
<objectID>EAE17E5F-AF89-4A35-9A3E-A182C644076C</objectID>
<resourceTypeID>386E81D6-901F-425F-B520-3F663716D861</resourceTypeID>
<dateAdded>2010-03-30T15:56:08</dateAdded>
</t>
</ChangedData>
</AuditMsg>'

select 
  t.n.value('(collectionID)[1]', 'varchar(50)') as collectionID
, t.n.value('(objectID)[1]', 'varchar(50)') as objectID
, t.n.value('(resourceTypeID)[1]', 'varchar(50)') as resourceTypeID
, t.n.value('(dateAdded)[1]', 'varchar(50)') as dateAdded
from @xml.nodes('/AuditMsg/ChangedData/t') t(n)
5 апр 11, 11:50    [10472428]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Sergey_Sver
Member

Откуда: Minsk, Belarus
Сообщений: 3
Спасибо, еще один вариант через OPENXML

DECLARE @idoc [INT]

EXEC sp_xml_preparedocument @idoc OUTPUT, @l_xml

SELECT *
FROM OPENXML( @idoc, '/AuditMsg/ChangedData/t', 1)
WITH (
collectionID uniqueidentifier './collectionID',
objectID uniqueidentifier './objectID',
resourceTypeID uniqueidentifier './resourceTypeID',
dateAdded datetime './dateAdded')

насколько он более ресурсоемкий?
5 апр 11, 13:11    [10473143]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Sergey_Sver
насколько он более ресурсоемкий?
Было.
5 апр 11, 23:26    [10476550]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Разбор XML в таблицу  [new]
beaver06
Member

Откуда:
Сообщений: 681
подскажите, пожалуйста, а как можно распарсить следующий xml:

<row ID="E98B9168-DFE4-4E43-84E9-28B2981ECABE" MessageType="01.001" StoreID="РО1234567" CreateDate="2013-02-12T15:15:04.707" CreateUTCDate="2013-02-12T11:15:04.707">
<XMLData>
<Root>Баян</Root>
</XMLData>
</row>
12 фев 13, 15:35    [13914673]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Гость333
Member

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

declare @x xml;

set @x =
N'<row ID="E98B9168-DFE4-4E43-84E9-28B2981ECABE" MessageType="01.001" StoreID="РО1234567" CreateDate="2013-02-12T15:15:04.707" CreateUTCDate="2013-02-12T11:15:04.707">
 <XMLData>
 <Root>Баян</Root>
 </XMLData>
</row> ';

select ID            = x.t.value('@ID', 'uniqueidentifier'),
       MessageType   = x.t.value('@MessageType', 'varchar(100)'),
       StoreID       = x.t.value('@StoreID', 'varchar(100)'),
       CreateDate    = x.t.value('@CreateDate', 'datetime'),
       CreateUTCDate = x.t.value('@CreateDate', 'datetime'),
       XMLData       = x.t.query('XMLData')
from @x.nodes('row') as x(t);
12 фев 13, 15:54    [13914862]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Гость333
Member

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

set @x =
N'<row ID="E98B9168-DFE4-4E43-84E9-28B2981ECABE" MessageType="01.001" StoreID="РО1234567" CreateDate="2013-02-12T15:15:04.707" CreateUTCDate="2013-02-12T11:15:04.707">
 <XMLData>
 <Root>Баян</Root>
 </XMLData>
</row> ';

select ID            = x.t.value('@ID', 'uniqueidentifier'),
       MessageType   = x.t.value('@MessageType', 'varchar(100)'),
       StoreID       = x.t.value('@StoreID', 'varchar(100)'),
       CreateDate    = x.t.value('@CreateDate', 'datetime'),
       CreateUTCDate = x.t.value('@CreateUTCDate', 'datetime'),
       XMLData       = x.t.query('XMLData')
from @x.nodes('row') as x(t);
12 фев 13, 15:56    [13914877]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
beaver06
Member

Откуда:
Сообщений: 681
Гость333,

спасибо
12 фев 13, 16:27    [13915135]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
beaver06
Member

Откуда:
Сообщений: 681
А это помогите, что-то не могу врубиться:

<Object Type="Рассылка: Морда">
<Header Date="2010.07.09 22:06:09" Number="4553337" SID="33333" />
<TabularSection TabularSectionName="Мячик">
<Row GID="756756756" Count="1" Price="150" />
</TabularSection>
</Object>
14 фев 13, 11:56    [13925040]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Гость333
Member

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

Это что, нужно раскидать XML по двум связанным таблицам?
14 фев 13, 12:21    [13925295]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
beaver06
Member

Откуда:
Сообщений: 681
Гость333,

нет, мне надо получить одну таблицу, с полями:GID,Count,Price
14 фев 13, 12:26    [13925338]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
beaver06,

DECLARE @xml xml
SET @xml ='<Object Type="Рассылка: Морда">
<Header Date="2010.07.09 22:06:09" Number="4553337" SID="33333" />
<TabularSection TabularSectionName="Мячик">
<Row GID="756756756" Count="1" Price="150" />
</TabularSection>
</Object> '

SELECT
    GID = x.t.value('@GID', 'varchar(100)'),
    [COUNT] = x.t.value('@COUNT', 'int'),
    Price = x.t.value('@Price', 'money')
FROM @xml.nodes('Object/TabularSection/Row') as x(t);
14 фев 13, 12:55    [13925564]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Гость333
Member

Откуда:
Сообщений: 3683
trew
beaver06,

DECLARE @xml xml
SET @xml ='<Object Type="Рассылка: Морда">
<Header Date="2010.07.09 22:06:09" Number="4553337" SID="33333" />
<TabularSection TabularSectionName="Мячик">
<Row GID="756756756" Count="1" Price="150" />
</TabularSection>
</Object> '

SELECT
    GID = x.t.value('@GID', 'varchar(100)'),
    [COUNT] = x.t.value('@Count', 'int'),
    Price = x.t.value('@Price', 'money')
FROM @xml.nodes('Object/TabularSection/Row') as x(t);

Поправил. В XML регистр символов имеет значение.
14 фев 13, 13:05    [13925644]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
beaver06
Member

Откуда:
Сообщений: 681
Гость333,

спасибо большое. Теперь разобрался как парсить xml
14 фев 13, 13:17    [13925769]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Разбор XML в таблицу  [new]
dizi
Member

Откуда:
Сообщений: 14
Предположим, у меня есть xml, в котором находятся данные вида
<Document>
<Product Number="13" Description="Пальто женское" Art="12545845" Model="Viola">
<Value Price="8500" Date="20100519" />
</Product>
....
Он находится в переменной @XmlCol

Я получаю две таблицы

select Number = x.t.value('@Number', 'int')
, Description = x.t.value('@Description', 'varchar(250)')
, Art = x.t.value('@Art', 'varchar(250)')
, Model = x.t.value('@Model', 'varchar(250)')
from @XmlCol.nodes('/Document/Product') as x(t);

select Price = x.t.value('@Price', 'float')
, Date = x.t.value('@Date', 'datetime')
from @XmlCol.nodes('/Document/Product/Value') as x(t);

Но как их связать между собой? По номеру строки?
9 дек 16, 15:36    [19985320]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
SELECT
  [Number] = x.t.value('@Number', 'int'),
  [Description] = x.t.value('@Description', 'varchar(250)'),
  [Art] = x.t.value('@Art', 'varchar(250)'),
  [Model] = x.t.value('@Model', 'varchar(250)'),
  [Price] = x.t.value('Value[1]/@Price', 'float')
FROM 
  @XmlCol.nodes('/Document/Product') as x(t)
;
9 дек 16, 16:26    [19985604]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Сорри, не увидел, что там M:1
Вариант №1
DECLARE @xmlCol XML = '
  <Document>
  <Product Number="13" Description="Пальто женское" Art="12545845" Model="Viola">
  <Value Price="8500" Date="20100519" />
  </Product>
  </Document>'

SELECT
  [Number] = x.t.value('@Number', 'int'),
  [Description] = x.t.value('@Description', 'varchar(250)'),
  [Art] = x.t.value('@Art', 'varchar(250)'),
  [Model] = x.t.value('@Model', 'varchar(250)'),
  [Price] = v.t.value('@Price', 'float'),
  [PriceDate] = v.t.value( '@Date', 'DATE' )
FROM 
  @XmlCol.nodes('/Document/Product') as x(t)
  CROSS APPLY x.t.nodes( 'Value' ) AS v(t)
;

Вариант №2
SELECT
  [Number] = x.t.value('@Number', 'int'),
  [Description] = x.t.value('@Description', 'varchar(250)'),
  [Art] = x.t.value('@Art', 'varchar(250)'),
  [Model] = x.t.value('@Model', 'varchar(250)')
FROM  
  @XmlCol.nodes('/Document/Product') as x(t)
;
SELECT
  [Number] = v.t.value('../@Number', 'int'),
  [Price] = v.t.value('@Price', 'float'),
  [Date] = v.t.value( '@Date', 'DATE' )
FROM 
  @XmlCol.nodes( '/Document/Product/Value' ) AS v(t)
;

Но эта задача к SQL вообще никакого отношения не имеет...
9 дек 16, 16:31    [19985622]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
dizi
Member

Откуда:
Сообщений: 14
Спасибо!
Извиняюсь, если к sql отношения не имеет.

Я уж было стала подниматься на уровень вверх так
select Номер = x.t.value('../@Number', 'int')
from @XmlCol.nodes('/Document/Product/Value') as x(t)

работает долго. Вариант с CROSS APLLY гораздо быстрее!
9 дек 16, 18:19    [19986224]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
uaggster
Member

Откуда:
Сообщений: 867
dizi, посмотрите вот эту презентацию Сергея Сыроватченко:

Он, кстати, тут на форуме обретается.
Из разряда "это должен знать каждый".
12 дек 16, 16:03    [19994147]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
ucs13
Member

Откуда:
Сообщений: 8
помогите, пожалуйста, разобрать такой xml в таблицу:
важно - Название атрибута "name" заранее не известно, оно может быть каким угодно.

огромное спасибо!

<IdentifierData>
<Item Name="Holder">Александр Македонский</Item>
<Item Name="Text">dddasasasasa</Item>
<Item Name="CardType">Standard </Item>
<Item Name="MaskedNumber">***1234</Item>
</IdentifierData>
<IdentifierData>
<Item Name="Holder">Петров Петр</Item>
<Item Name="Sale">dddaaaddbbba</Item>
<Item Name="Adress">dd7778asa</Item>
<Item Name="CardType">Standard </Item>
<Item Name="MaskedNumber">***1234</Item>
</IdentifierData>
<IdentifierData>
<Item Name="Holder">Иванов Иван</Item>
<Item Name="CardType">Standard </Item>
<Item Name="Buklet">dddaaaddbbba</Item>
<Item Name="Segment">dd7778asa</Item>
<Item Name="MaskedNumber">***1234</Item>
</IdentifierData>
8 фев 17, 18:01    [20194351]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
Владислав Колосов
Member

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

сделайте таблицу из двух колонок Name и Data, помещайте данные в неё.
9 фев 17, 13:30    [20196611]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML в таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
ucs13
помогите, пожалуйста, разобрать такой xml в таблицу
Сколько и каких столбцов должно быть нам самим придумать?
9 фев 17, 14:01    [20196772]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить