0

I am struggling to extract data from an XML file. The file for all intense and purposes looks like this:

<Message>
  <Code>Hello </Code>
  <Code>bye </Code>
</Message>

At the moment I am using the Value function.

However the data I get returned is only ever "Hello". How do I return all data in a tabular format, like so:

enter image description here

closed as unclear what you're asking by Philᵀᴹ, Vérace, Tom V, Colin 't Hart, mustaccio Nov 6 '17 at 14:05

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Please tag your RDBMS. – McNets Nov 6 '17 at 10:16
2

If your question is about MS-Sql you can use this syntax:

DECLARE @data xml;

SET @data = 
'<Message>
  <Code>Hello </Code>
  <Code>bye </Code>
</Message>';

SELECT d.lin.value('.', 'varchar(100)') as [Code]
FROM   @data.nodes('Message/Code') d(lin)
GO
| Code   |
| :----- |
| Hello  |
| bye    |

dbfiddle here

Not the answer you're looking for? Browse other questions tagged or ask your own question.