CODEWITHSUNDEEP
sql-serverservice-broker
pepr
pepr

Reputation: 20794

Service Broker -- how to extract the rows from the XML message?

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

Upvotes: 2

Views: 1814

Answers (2)

Jānis
Jānis

Reputation: 2266

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

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294407

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

Upvotes: 4

Related Questions