2

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?

Community
  • 1
  • 1
pepr
  • 20,112
  • 15
  • 76
  • 139
  • @marc_s: I want to have four rows in the `tab2` where the `e` column contains ``, then (next row of the table), ``, 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 Jul 20 '12 at 12:06

1 Answers1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 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 Jul 20 '12 at 13:09
  • 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 Jul 21 '12 at 10:01
  • Thanks! This is exactly what I wanted to achieve. – pepr Jul 22 '12 at 16:03