Skip to content

Copy data

SQL
1
2
    SELECT [id], [dictionary], [username], [name], [label], [title], [desktop], [mobile], [width] 
    FROM [dbo].[columns]  row for xml auto,ROOT('data')

Przykład utworzonego obiektu

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
DECLARE @XML XML
SET @XML = 
'<data>
<row id="1" dictionary="documentin" name="itemid" label="test" title="test title" desktop="0" />
<row id="2" dictionary="documentin" name="itemcode" label="Indeks Mag." title="Indeks Magazynu" desktop="1" />
<row id="3" dictionary="documentin" name="itemname" label="Nazwa Mag." title="Nazwa Magazynu" desktop="1" width="350" />
<row id="4" dictionary="documentin" name="itemnameshort" desktop="0" />
<row id="5" dictionary="documentin" name="itemnamecommercial" desktop="0" />
<row id="6" dictionary="documentin" name="itemmemo" desktop="0" />
<row id="7" dictionary="documentin" name="itemprice" label="Ostatnia cena zakupu" title="Ostatnia cena zakupu" desktop="1" />
<row id="8" dictionary="documentin" name="row" desktop="0" />
<row id="9" dictionary="documentin" name="itemmaxprice" desktop="0" />
<row id="10" dictionary="documentin" name="itemmaterial" desktop="0" />
... data
</data>'

Srypt do skopiowania

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
SET IDENTITY_INSERT  [dbo].[columns]  ON;  
insert into [dbo].[columns] 
(
[id], [dictionary], [username], [name], [label], [title], [desktop], [mobile], [width]
)

select 
    m.c.value('@id', 'varchar(max)') as id,
    m.c.value('@dictionary', 'varchar(max)') as dictionary,
    m.c.value('@username', 'varchar(max)') as username,
    m.c.value('@name', 'varchar(max)') as name,
    m.c.value('@label', 'varchar(max)') as label,
    m.c.value('@title', 'varchar(max)') as title,
    m.c.value('@desktop', 'varchar(max)') as desktop,
    m.c.value('@mobile', 'varchar(max)') as mobile,
    m.c.value('@width', 'varchar(max)') as width
FROM   @xml.nodes('//data/row')   as m(c)

SET IDENTITY_INSERT  [dbo].[columns]  OFF;