CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005xquery
Kenneth Cochran
Kenneth Cochran

Reputation: 12064

How do I split an xml variable using xquery?

I have a scalar xml variable:

DECLARED @XML xml =
'<rows>
    <row>
        <column1 attrib="" />
        <column2 attrib="" />
    </row>
    <!-- ... -->
</rows>';

I would like to split the data so that each row's xml is assigned to a new record in a table:

Id | ... | XML
1  |     | '<row><column1 attrib="" /><column2 attrib="" /></row>'
2  |     | etc.
3  |     | etc.

I haven't quite grasped xquery so I'm having trouble writing an insert statement that does what I want.

INSERT into MyTable( [XML])
SELECT @XML.query('row')

Now I know something is happening but it appears rather than doing what I intended and inserting multiple new records it is inserting a single record with an empty string into the [XML] column.

What am I not getting?

Clarification

I am not trying to get the inner text, subelements or attributes from each row using value(...). I am trying to capture the entire <row> element and save it to a column of type xml

I've experimented with nodes(...) and come up with:

INSERT into MyTable([XML])
SELECT C.query('*')
FROM @XML.nodes('rows/row') T(C)

Which is closer to what I want but the results don't include the outer <row> tag, just the <column*> elements it contains.

Upvotes: 1

Views: 3987

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

T-SQL Script:

SET ANSI_WARNINGS ON;

DECLARE @x XML =
'<rows>
    <row Atr1="11" />
    <row Atr1="22" Atr2="B" />
    <row Atr1="33" Atr2="C" />
</rows>';

DECLARE @Table TABLE(Id INT NOT NULL, [XMLColumn] XML NOT NULL);

INSERT  @Table (Id, XMLColumn)
SELECT  ROW_NUMBER() OVER(ORDER BY @@SPID) AS Id,
        a.b.query('.') AS [XML]
FROM    @x.nodes('//rows/row') AS a(b);

SELECT  *
FROM    @Table t;

Results:

Id XMLColumn
-- --------------------------
1  <row Atr1="11" />
2  <row Atr1="22" Atr2="B" />
3  <row Atr1="33" Atr2="C" />

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294217

You need to use .nodes(...) to project each <row .../> as a row and the extract the attributes of interest using .value(...). Something like:

insert into MyTable(XML)
select x.value('text()', 'nvarchar(max)') as XML
from @XML.nodes(N'/rows/row') t(x);

text() will select the inner text of each <row .../>. You should use the appropriate expression (eg. node() or @attribute etc, see XPath examples), depending on what you want from the row (your example does not make it clear at all, with all those empty elements...).

Upvotes: 1

Related Questions