如何在SQL Server表中解析XML数据

Question

I have SQL table:

Create table
(
ID varchar(50) not null,
Action nvarchar(max) null
)

操作列包含XML数据。 格式：

<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order>   
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
    </oo_master> 
    </oo_outbound_order>

如何解析这一列？结果应该像这样：

CODE    NAME
123     Branan

- Kirill Rodeonov

Answer 1

有很多关于使用TSQL解析XML的教程文章。例如，http://www.sqlserver.info/syntax/parse-xml-with-sql-server/

DECLARE @xml xml
SET @xml = 
'<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order>   
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
    </oo_master> 
    </oo_outbound_order>'

SELECT 
    n.value('(./Code/text())[1]','int') as CODE
 , n.value('(./Name/text())[1]','Varchar(50)') as NAME
FROM @xml.nodes('/oo_outbound_order/oo_master') as a(n)

- vadzim dvorak

---

谢谢。我可以使用表列而不是@xml变量吗？ - Kirill Rodeonov

Answer 2

尝试以下方法：

尝试以下方法：

DECLARE @XMLData XML = '
<oo_outbound_order>   
    <oo_master>
        <Code>123</Code>
        <Name>Branan</Name> 
    </oo_master> 
</oo_outbound_order>'

SELECT
    [Code] = Node.Data.value('Code', 'INT'),
    [Name] = Node.Data.value('Name', 'NVARCHAR(20)')
    FROM @XMLData.nodes('/oo_outbound_order/oo_master') Node(Data)

- Jayasurya Satheesh

---

如果您在 XQuery 结尾不使用 []，它总是会返回一个集合。如果您需要单个值，请使用 Node.Data.value('Code[1]', 'INT')。 - corentin_chap

Answer 3

将XML数据存储在字符串列中是一个非常糟糕的想法。

更糟糕的是，你正在存储一个字符串，它声称“我被编码为utf-8！”，但它存储在NVARCHAR列中，该列是UCS-2（几乎与utf-16相同）。

这意味着，在使用之前，您必须修复并将字符串转换为XML。每次访问时都需要执行这些昂贵的操作。如果可以更改此设置，应该将XML存储在本地化的类型列中。

尽管如此，这也是可以实现的。请尝试以下方法。

Create table #temp
(
ID varchar(50) not null,
[Action] nvarchar(max) null
)

INSERT INTO #temp(ID,[Action]) VALUES
('test 1',
'<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order>   
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
    </oo_master> 
    </oo_outbound_order>');

SELECT t.ID
      ,ActionXml.value('(/oo_outbound_order/oo_master/Code/text())[1]','nvarchar(max)') AS CODE
      ,ActionXml.value('(/oo_outbound_order/oo_master/Name/text())[1]','nvarchar(max)') AS [Name]
FROM #temp t
CROSS APPLY(SELECT CAST(REPLACE(t.[Action],'encoding="UTF-8"','encoding="UTF-16"') AS XML) ActionXml) A;

drop table #temp;

提示：如果没有重复元素，则不需要使用.nodes()，正如其他答案所建议的那样...

- Shnugo