SQL Server -- how to shred one multielement XML to single-element XML values inserted into table?

Question

I am fighting with @x.nodes('...') as I am new to XQuery. I do have the XML variable @x constructed the following way:

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

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

When its content is displayed, 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. How can shred the single multielement XML value to many single-element value in the destination table? (I am aware of the official nodes() Method (xml Data Type) documentation page, but I am doing something wrong.)

CREATE TABLE tab2 (e XML);

??? ... @x.nodes('//row') ... ???

Thanks, Petr

P.S. The question is loosely related to Service Broker -- how to extract the rows from the XML message?

@marc_s: I want to have four rows in the tab2 where the e column contains <row a="1" b="11" c="111" />, then (next row of the table), <row a="2" b="22" c="222" />, etc. The motivation is to unify the situation when the Service Broker sends a single row of the original table as the result of insertion of the row (trigger), and the situation when the block of already existing rows is to be extracted from the original table. — pepr
– pepr, Commented Jul 20, 2012 at 12:06

marc_s · Accepted Answer · 2012-07-21 10:19:10Z

1

OK - so you have an XML variable that contains that XML you posted - and you need to shred this into its own individual bits?

Try something like this:

SELECT
    value_a = c.value('(@a)[1]', 'int'),
    value_b = c.value('(@b)[1]', 'int'),
    value_c = c.value('(@c)[1]', 'int') 
FROM @x.nodes('/row') AS T(c)

That gives me an output of :

enter image description here

Is that what you're looking for?

Update: ok, if I understand you correctly, this is what you want:

SELECT
    c.query('.')
FROM @x.nodes('/row') AS T(c)

Output:

enter image description here

edited Jul 21, 2012 at 10:19

answered Jul 20, 2012 at 12:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pepr Over a year ago

No, it does not solve my problem. The @x is actually obtained as the value of the Service Broker message. The example uses only the commands to simulate the same value -- this was actually done at the initiator side. Then the value is obtained at the target side of the Service Broker. Sometimes the value contain a single row element, sometimes it contains more row elements. I want to split the multi into more single, as if more single row messages arrived. I already know how to extract the details from the multirow. I want to understand how to extract the whole rows, not the details.

pepr Over a year ago

Thanks. I have learned a similar one earlier -- see the answer at the reference near bottom of the question. I would like to get a single column with values of the xml type in the same form. The only difference I want to have the XML representation of the rows be separated -- one row as one value, instead of many rows as one value. The purpose of the question is only to learn if it is possible, and how to do that.

pepr Over a year ago

Thanks! This is exactly what I wanted to achieve.

Collectives™ on Stack Overflow

SQL Server -- how to shred one multielement XML to single-element XML values inserted into table?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related