2

Say I have the following situation (for demonstration). The simple table content is converted to an XML value and sent via Service Broker to another SQL server where the result of the SELECT is to be stored in the non-XML form (i.e. usual, simple database table). Let's try:

CREATE TABLE tab (a int, b int, c int);
GO

INSERT INTO tab (a, b, c) VALUES (1, 11, 111);
INSERT INTO tab (a, b, c) VALUES (2, 22, 222);
INSERT INTO tab (a, b, c) VALUES (3, 33, 333);
INSERT INTO tab (a, b, c) VALUES (4, 44, 444);
GO

SELECT * FROM tab FOR XML RAW, TYPE;
GO

When capturing the value to the message, it looks like:

<row a="1" b="11" c="111" />
<row a="2" b="22" c="222" />
<row a="3" b="33" c="333" />
<row a="4" b="44" c="444" />

i.e. single multiline string. Say, I create the exactly same table structure at the destination machine:

CREATE TABLE tab_destination (a int, b int, c int);

What is the best way to extract the rows from the @msg, and how to put them to the destination table?

Thanks, Petr

pepr
  • 20,112
  • 15
  • 76
  • 139

2 Answers2

4
CREATE TABLE tab (a int, b int, c int); 
GO 

INSERT INTO tab (a, b, c) VALUES (1, 11, 111); 
INSERT INTO tab (a, b, c) VALUES (2, 22, 222); 
INSERT INTO tab (a, b, c) VALUES (3, 33, 333); 
INSERT INTO tab (a, b, c) VALUES (4, 44, 444); 
GO 

CREATE TABLE tab_destination (a int, b int, c int); 
go

declare @x xml = (SELECT * FROM tab FOR XML RAW, TYPE); 

insert into tab_destination (a, b, c)
select 
    x.value('@a', 'int'),
    x.value('@c', 'int'),
    x.value('@b', 'int')
    from @x.nodes('//row') t(x);
GO 

select * from tab_destination;
go

Time to read xml Data Type Methods

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks, it works. Remus and @Janis: I am reading the recommended doc, but I do not understand one related detail. Please, have a look at http://stackoverflow.com/questions/11578904/sql-server-how-to-shred-one-multielement-xml-to-single-element-xml-values-ins – pepr Jul 20 '12 at 12:01
1

And other option (i would prefer Remus Rusanu example though.. If a lots of columns and table structures are the same, this helps get lazy):

declare @x xml = (SELECT * FROM tab FOR XML RAW, root('tab'), TYPE); 

Declare @docHandle int  
EXEC sp_xml_preparedocument @docHandle OUTPUT, @x

Select *
FROM OPENXML(@docHandle, 'tab//', 1) 
With dbo.Tab

EXEC sp_xml_removedocument @docHandle
Jānis
  • 2,216
  • 1
  • 17
  • 27