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

Откуда:
Сообщений: 101
Всем привет!

CREATE TABLE [dbo].[T_DATA_MAIN](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[N] [float] NULL,
	[S] [nvarchar](4000) NULL,
	[D] [datetime] NULL,
 CONSTRAINT [PK_T_DATA_MAIN] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
GO


Выгружаю данные из этой таблицы с помощью select..for xml.. Затем пытаюсь извлечь из полученных данных строки таблицы:

DECLARE @idoc int 
declare @txt nvarchar(max)=N'<BACKUP>
<T_DATA_MAIN>
<ROW_T_DATA_MAIN ID="1" N="1.000000000000000e+000" S="строкА 1" D="2013-03-23T22:04:00"/>
</T_DATA_MAIN></BACKUP>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @txt
select *
from openxml(@idoc,'/BACKUP/T_DATA_MAIN/ROW_T_DATA_MAIN',1)  
with T_DATA_MAIN


В результирующем наборе отсутствует поле ID. Если отключить в таблице IDENTITY для поля ID, то возвращаются все поля. Можно ли получить с помощью OPENXML набор всех полей таблицы не отключая IDENTITY ?
23 мар 13, 23:45    [14087512]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
Expecting
Если отключить в таблице IDENTITY для поля ID, то возвращаются все поля.
А XML как при этом меняется?
24 мар 13, 00:28    [14087692]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
alexeyvg
Expecting
Если отключить в таблице IDENTITY для поля ID, то возвращаются все поля.
А XML как при этом меняется?
Можно конечно так писать...
with (	[ID] [int] '@ID',
	[N] [float] '@N',
	[S] [nvarchar](4000) '@S',
	[D] [datetime] '@D')
24 мар 13, 00:39    [14087743]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
Expecting
Member

Откуда:
Сообщений: 101
alexeyvg
пропущено...
А XML как при этом меняется?


XML при этом не меняется

alexeyvg
Можно конечно так писать...
with (	[ID] [int] '@ID',
	[N] [float] '@N',
	[S] [nvarchar](4000) '@S',
	[D] [datetime] '@D')


К сожалению, так не хотелось бы делать, потому что запрос формируется динамически, формировать же динамически выражение with c перечислением колонок и их типов загромоздит код..

В общем задача такая: сохранить данные таблицы в XML-формат, затем восстановить эти данные
24 мар 13, 10:53    [14088289]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6178
можно применить вот такой хак:
CREATE TABLE [Production].[ProductReview](
  ProductReviewID int IDENTITY(1,1) NOT NULL,
  ProductID int NOT NULL,
  ReviewerName dbo.Name NOT NULL,
  ReviewDate datetime NOT NULL,
  EmailAddress nvarchar(50) NOT NULL,
  Rating int NOT NULL,
  Comments nvarchar(3850) NULL,
  ModifiedDate datetime NOT NULL
)

use AdventureWorks2012
go
create view Production.vw_ProductReview as
select
  0 ProductReviewID,
  ProductID, ReviewerName, ReviewDate, EmailAddress, Rating, Comments, ModifiedDate
  from Production.ProductReview;

declare
  @h int,
  @x nvarchar(max);
select @x=t.x from (
  select * from Production.ProductReview for xml raw, root
) t(x);
exec sp_xml_preparedocument @h out, @x;
select * from openxml(@h, '/root/row', 1) with Production.vw_ProductReview;
exec sp_xml_removedocument @h;
24 мар 13, 11:34    [14088387]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6178
Можно даже вот так:
create view Production.vw_ProductReview as
select
  ProductReviewID+0 ProductReviewID,
  ProductID, ReviewerName, ReviewDate, EmailAddress, Rating, Comments, ModifiedDate
  from Production.ProductReview;
24 мар 13, 11:38    [14088393]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
Expecting
Member

Откуда:
Сообщений: 101
Сон Веры Павловны
Можно даже вот так:
create view Production.vw_ProductReview as
select
  ProductReviewID+0 ProductReviewID,
  ProductID, ReviewerName, ReviewDate, EmailAddress, Rating, Comments, ModifiedDate
  from Production.ProductReview;


да, вариант. Но все равно придется генерировать скрипт на создание вьюхи с перечислением всех полей таблицы + вытаскивать наименование поля IDENTITY. Это не намного проще чем генерировать WITH с указанием поля, типа и атрибута
24 мар 13, 12:24    [14088470]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6178
Expecting
Но все равно придется генерировать скрипт на создание вьюхи с перечислением всех полей таблицы + вытаскивать наименование поля IDENTITY. Это не намного проще чем генерировать WITH с указанием поля, типа и атрибута

Ну все же не возиться с вытаскиванием определения типа - что при наличии в таблице полей decimal/numeric, varchar определенной длины и (max) и т.п. будет сильно загроммождать код.
Да, еще как вариант - делать просто openxml без with, и самому вытаскивать данные из edge table.
24 мар 13, 13:15    [14088652]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
Сон Веры Павловны
Да, еще как вариант - делать просто openxml без with, и самому вытаскивать данные из edge table.
Это конечно тоже сильно загромождает код, но зато (как ни странно) выбрка будет намного быстрее.
24 мар 13, 13:35    [14088697]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
Expecting
Member

Откуда:
Сообщений: 101
alexeyvg
Сон Веры Павловны
Да, еще как вариант - делать просто openxml без with, и самому вытаскивать данные из edge table.
Это конечно тоже сильно загромождает код, но зато (как ни странно) выбрка будет намного быстрее.


Правильно ли я понимаю, что таким образом получу набор данных с текстовыми значениями. Не нужно ли мне будет их приводить к нужному типу делая insert into .. select .. ?
24 мар 13, 16:39    [14089212]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6178
Expecting
Правильно ли я понимаю, что таким образом получу набор данных с текстовыми значениями.

Не просто с текстовыми - с значениями типа ntext: http://msdn.microsoft.com/en-us/library/ms186918.aspx
Т.к. я сомневаюсь, что у вас xml содержит инлайновый DTD или схему, то поле datatype в edge table у всех записей будет содержать null - тип придется вытаскивать из information_schema.columns (или sys.columns), и приводить динамически.
24 мар 13, 16:59    [14089235]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
Expecting
Member

Откуда:
Сообщений: 101
Сон Веры Павловны
Expecting
Правильно ли я понимаю, что таким образом получу набор данных с текстовыми значениями.

Не просто с текстовыми - с значениями типа ntext: http://msdn.microsoft.com/en-us/library/ms186918.aspx
Т.к. я сомневаюсь, что у вас xml содержит инлайновый DTD или схему, то поле datatype в edge table у всех записей будет содержать null - тип придется вытаскивать из information_schema.columns (или sys.columns), и приводить динамически.


Мда, вернулся к тому, от чего хотел уйти..
24 мар 13, 17:41    [14089309]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
invm
Member

Откуда: Москва
Сообщений: 9724
create view dbo.vDummy as select ID + 0 as ID, N, S, D from [dbo].[T_DATA_MAIN]
go

DECLARE @idoc int 

declare @txt nvarchar(max)=N'<BACKUP>
<T_DATA_MAIN>
<ROW_T_DATA_MAIN ID="1" N="1.000000000000000e+000" S="строкА 1" D="2013-03-23T22:04:00"/>
</T_DATA_MAIN></BACKUP>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @txt
select *
from openxml(@idoc,'/BACKUP/T_DATA_MAIN/ROW_T_DATA_MAIN',1)  
with dbo.vDummy

exec sp_xml_removedocument @idoc 
24 мар 13, 17:51    [14089336]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
invm
Member

Откуда: Москва
Сообщений: 9724
Или
DECLARE @idoc int 

declare @txt nvarchar(max)=N'<BACKUP>
<T_DATA_MAIN>
<ROW_T_DATA_MAIN ID="1" N="1.000000000000000e+000" S="строкА 1" D="2013-03-23T22:04:00"/>
</T_DATA_MAIN></BACKUP>'

select top (0) ID + 0 as ID, N, S, D into #t from [dbo].[T_DATA_MAIN]

EXEC sp_xml_preparedocument @idoc OUTPUT, @txt
select *
from openxml(@idoc,'/BACKUP/T_DATA_MAIN/ROW_T_DATA_MAIN',1)  
with #t

exec sp_xml_removedocument @idoc 
24 мар 13, 17:54    [14089343]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
Expecting,

О, вот как можно обмануть, спасибо invm за подсказку:
select t.* into #t 
from sysobjects
cross join (
	select *
	from [dbo].[T_DATA_MAIN] where 1=0
) t
	
DECLARE @idoc int 
declare @txt nvarchar(max)=N'<BACKUP>
<T_DATA_MAIN>
<ROW_T_DATA_MAIN ID="1" N="1.000000000000000e+000" S="строкА 1" D="2013-03-23T22:04:00"/>
</T_DATA_MAIN></BACKUP>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @txt
select *
from openxml(@idoc,'/BACKUP/T_DATA_MAIN/ROW_T_DATA_MAIN',1)  
with #t
24 мар 13, 18:48    [14089455]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
Expecting
Member

Откуда:
Сообщений: 101
alexeyvg
Expecting,

О, вот как можно обмануть, спасибо invm за подсказку:
select t.* into #t 
from sysobjects
cross join (
	select *
	from [dbo].[T_DATA_MAIN] where 1=0
) t
	
DECLARE @idoc int 
declare @txt nvarchar(max)=N'<BACKUP>
<T_DATA_MAIN>
<ROW_T_DATA_MAIN ID="1" N="1.000000000000000e+000" S="строкА 1" D="2013-03-23T22:04:00"/>
</T_DATA_MAIN></BACKUP>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @txt
select *
from openxml(@idoc,'/BACKUP/T_DATA_MAIN/ROW_T_DATA_MAIN',1)  
with #t



Спасибо всем огромное! Получилось, хотя для инсерта придется явно генерировать список колонок, иначе не дает вставить значение в поле IDENTITY. В итоге получается такой код для таблицы с полем IDENTITY:

DECLARE @idoc int 
declare @txt nvarchar(max)=N'<BACKUP>
<T_DATA_MAIN>
<ROW_T_DATA_MAIN ID="1" N="1.000000000000000e+000" S="строкА 1" D="2013-03-23T22:04:00"/>
</T_DATA_MAIN></BACKUP>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @txt

set identity_insert T_DATA_MAIN  on
select t.* into #t 
from sysobjects
cross join (select * from [T_DATA_MAIN] where 1=0) t
insert into T_DATA_MAIN [b](ID,N,S,D)[/b]-- список полей нужен при вставке в поле IDENTITY
select *
from openxml(@idoc,'BACKUP/T_DATA_MAIN/ROW_T_DATA_MAIN',1)  
with #t
set identity_insert T_DATA_MAIN  off
drop table #t
24 мар 13, 20:27    [14089657]     Ответить | Цитировать Сообщить модератору
 Re: OPENXML.. with table с полем identity  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
Expecting
Получилось, хотя для инсерта придется явно генерировать список колонок, иначе не дает вставить значение в поле IDENTITY
Вообще если данные вставляются только импортом, то можно IDENTITY убрать :-)
24 мар 13, 23:33    [14090010]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить