Reading a XML file into columns in SQL Server-CodePudding

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.

CodePudding user response：

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  |
 ------ ---------

CodePudding user response：

You can try this:

;WITH XMLNAMESPACES ('http://dataexample/rest/entit' AS ns)
SELECT TOP 4 
b.value('(.)[1]', 'varchar(50)') AS columna
FROM @dat.nodes('/ns:el/ns:ent') as a(b)

GO