Reading a XML file into columns in SQL Server

Question

This is the example of the XML input I need to parse:

declare @dat XML = '
<ns:el xmlns:ns="http://dataexample/rest/entit">
 <ns:ent href="/1">a - 10</ns:ent>
 <ns:ent href="/2">b - 20</ns:ent>
 <ns:ent href="/3">c - 30</ns:ent>
 <ns:ent href="/4">d - 40</ns:ent>
 <ns:ent href="/5">e - 50</ns:ent>
</ns:el>
';

with XMLNAMESPACES('http://dataexample/rest/entit' as ns)
select b.value('(.)[1]', 'nvarchar(2000)')  as columna
from @dat.nodes('/ns:el') as a(b)

But with my code I'm getting this: a - 10b - 20c - 30d - 40e - 50 . One single line but I need to reach this:

Ref Name
1   a- 10
2   b - 20
3   c - 30
4   d - 40
5   e - 50

What do I need to modify in the query to reach the expected result.

Answer 1

Please try the following.

Designating a namespace as a DEFAULT allows to prevent mushrooming of the namespace prefix in the XPath expressions.

SQL

DECLARE @dat XML = 
N'<ns:el xmlns:ns="http://dataexample/rest/entit">
 <ns:ent href="/1">a - 10</ns:ent>
 <ns:ent href="/2">b - 20</ns:ent>
 <ns:ent href="/3">c - 30</ns:ent>
 <ns:ent href="/4">d - 40</ns:ent>
 <ns:ent href="/5">e - 50</ns:ent>
</ns:el>';

;WITH XMLNAMESPACES (DEFAULT 'http://dataexample/rest/entit')
SELECT SUBSTRING(c.value('@href', 'VARCHAR(10)'), 2, 10) AS href
    , c.value('text()[1]', 'NVARCHAR(2000)') AS columna
FROM @dat.nodes('/el/ent') as t(c);

Output

+------+---------+
| href | columna |
+------+---------+
|    1 | a - 10  |
|    2 | b - 20  |
|    3 | c - 30  |
|    4 | d - 40  |
|    5 | e - 50  |
+------+---------+