Help with reading xml

Question

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:

score 2 · Answer 1 · answered Nov 06 '17 at 10:29

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

Help with reading xml

1 Answers1