查询XML类型数据列并插入到SQL Server表中(Query XML type data column and insert into SQL Server table)

 希望有人可以帮我解决SQL Server问题，在我们的一个表中，我有一个longtext数据类型，其中包含XML类型格式的数据，如下所示： 
 
<entry name=\"cleaned_sectors\" type=\"uint\">585937500</entry>\r\n<entry name=\"bad_sectors\" type=\"uint\">0</entry>\r\n<entry name=\"state\"
 
 通常，当我在表格中遇到XML时，它看起来像这样： 
 
<header>data</Header><Manufacturer>Dell</Manufacturer>
 
 我生成一个查询来获取数据，如下所示： 
 
SELECT 
    Tbl.Col.value('Header[1], 'nvarchar(50)'), Tbl.Col.value('manufacturer[1].'nvarchar(50)') 
FROM ...
 
 但是你会注意到这个数据不是标准的XML，并且每个标签都使用\“来包含，有没有一种方法可以查询包含的数据，然后将其导入到另一个表中？ 
 
 预期的输出将如下所示： 
 
Cleaned Sectors | Bad Sectors
585937500       | 0
 
 任何查询数据的帮助都会很棒，因为我一直在尝试几个小时。 谢谢。 

Hoping someone can help me with a SQL Server issue, In one of our tables I have a longtext data type which contains data in an XML type format as below:
 
<entry name=\"cleaned_sectors\" type=\"uint\">585937500</entry>\r\n<entry name=\"bad_sectors\" type=\"uint\">0</entry>\r\n<entry name=\"state\"
 
Generally when I have come across XML in a table it looks like this:
 
<header>data</Header><Manufacturer>Dell</Manufacturer>
 
And I generate a query to obtain the data like so:
 
SELECT 
    Tbl.Col.value('Header[1], 'nvarchar(50)'), Tbl.Col.value('manufacturer[1].'nvarchar(50)') 
FROM ...
 
However you will notice this data isn't standard XML and each tag is contained using \" is there a way I can query the data contained with the aim to then import this into another table?
 
Expected output would be something like:
 
Cleaned Sectors | Bad Sectors
585937500       | 0
 
Any help with querying the data would be brilliant as I have been trying for hours. Thank you.

原文：https://stackoverflow.com/questions/32467187