将XML文件读取到SQL Server的列中

Question

这是我需要解析的XML输入示例:

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)

但是用我的代码我得到这个:a - 10b - 20c - 30d - 40e - 50。一行，但我需要达到这个:

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

我需要在查询中修改什么才能达到预期的结果

Answer 1

请尝试以下操作

将命名空间指定为DEFAULT允许防止在XPath表达式中出现命名空间前缀。

/p>

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

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

Answer 2

你可以试试:

;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