Транспонирование строк в столбцы на сервере sql

0

Эта

DECLARE @yourXML XML =
'<root> <records> <record> <field name="doccreated" type="DateTime">2002-09-28T18:17:32.12Z</field> <field name="docmodified" type="DateTime">2002-11-19T15:18:26.19Z</field> <field name="Version" type="Double">3.1.43</field> </record> <record> <field name="doccreated" type="DateTime">2002-09-28T18:17:32.09Z</field> <field name="docmodified" type="DateTime">2002-12-13T15:53:50.66Z</field> <field name="Version" type="Double">3.1.43</field></record> </records> </root>'

SELECT 
  doccreated  = x.record.value('(field[@name="doccreated"]/text())[1]', 'date'),
  docmodified = x.record.value('(field[@name="docmodified"]/text())[1]', 'date'),
  [version]   = x.record.value('(field[@name="Version"]/text())[1]', 'varchar(10)')
FROM @yourXML.nodes('/root/records/record') x(record);

Результаты

doccreated docmodified version
---------- ----------- ----------
2002-09-28 2002-11-19  3.1.43
2002-09-28 2002-12-13  3.1.43
SQL, SQL-сервер, TSQL, SQL-сервер-2014-экспресс,

sql,sql-server,tsql,sql-server-2014-express,

-3

Ответов: 1

0

Эта

DECLARE @yourXML XML =
'<root> <records> <record> <field name="doccreated" type="DateTime">2002-09-28T18:17:32.12Z</field> <field name="docmodified" type="DateTime">2002-11-19T15:18:26.19Z</field> <field name="Version" type="Double">3.1.43</field> </record> <record> <field name="doccreated" type="DateTime">2002-09-28T18:17:32.09Z</field> <field name="docmodified" type="DateTime">2002-12-13T15:53:50.66Z</field> <field name="Version" type="Double">3.1.43</field></record> </records> </root>'

SELECT 
  doccreated  = x.record.value('(field[@name="doccreated"]/text())[1]', 'date'),
  docmodified = x.record.value('(field[@name="docmodified"]/text())[1]', 'date'),
  [version]   = x.record.value('(field[@name="Version"]/text())[1]', 'varchar(10)')
FROM @yourXML.nodes('/root/records/record') x(record);

Результаты

doccreated docmodified version
---------- ----------- ----------
2002-09-28 2002-11-19  3.1.43
2002-09-28 2002-12-13  3.1.43
SQL, SQL-сервер, TSQL, SQL-сервер-2014-экспресс,
Похожие вопросы
Яндекс.Метрика