Get rows from XML array using OPENXML

Question

I have following XML:

<root>
  <fields>
    <field>Some Name</field>
    <field>Another Name</field>
  </fields>
</root>

As the result I would like to have:

Some Name
Another Name

To achieve that I am trying to execute following query:

DECLARE @XML XML = N'
<root>
  <fields>
    <field>Some Name</field>
    <field>Another Name</field>
  </fields>
</root>';


DECLARE @idoc INT;
EXEC sys.sp_xml_preparedocument @idoc OUTPUT, @XML;
SELECT  *
FROM    OPENXML(@idoc, '/root/fields',2)
WITH (Name VARCHAR(300)  './field');
EXEC sys.sp_xml_removedocument @idoc;

But I am getting only the first record...

Devart · Accepted Answer · 2016-06-03 10:50:28Z

4

DECLARE @XML XML = N'
<root>
  <fields>
    <field>Some Name</field>
    <field>Another Name</field>
  </fields>
</root>';

-- XQuery
SELECT t.c.value('(./text())[1]', 'VARCHAR(300)')
FROM @XML.nodes('/root/fields/field') t(c)

-- OpenXML
DECLARE @idoc INT
EXEC sys.sp_xml_preparedocument @idoc OUTPUT, @XML

SELECT *
FROM OPENXML(@idoc, '/root/fields/*',2) WITH (Name VARCHAR(300) '.')

EXEC sys.sp_xml_removedocument @idoc

answered Jun 3, 2016 at 10:50

Devart

120k23 gold badges172 silver badges189 bronze badges

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

Collectives™ on Stack Overflow

Get rows from XML array using OPENXML

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related