查询XML类型数据列并插入SQL Server表

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:

希望有人可以帮我解决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\"

Generally when I have come across XML in a table it looks like this:

通常,当我在表中遇到XML时,它看起来像这样:

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

但是你会注意到这个数据不是标准的XML,并且每个标签都包含在“有没有办法我可以查询包含的数据,然后将其导入另一个表?

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.

任何有关查询数据的帮助都会很棒,因为我已经尝试了几个小时。谢谢。

1 个解决方案

#1

Clean your input like and then convert to XML and use as always.

清理您的输入,然后转换为XML并一如既往地使用。

SELECT
  CAST(
    REPLACE(
      REPLACE('<entry name=\"cleaned_sectors\" type=\"uint\">585937500</entry>\r\n'
      ,'\"', '"')
   ,'\r\n', '')
  AS XML)