sql-server - 尝试读取 XML 的节点值时出现 XML 解析错误

Question

我有一个 XML 文件 (TestArticles.xml)，我需要将其导入 SQL SERVER 2014 并从各个节点读取数据并将其插入到同一数据库中的其他几个表中。

TestArticles.xml

<?xml version="1.0" encoding="UTF-8"?>
<Articles>
<sv:node sv:name="test1"
    xmlns:sv="http://www.jcp.org/jcr/sv/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <sv:property sv:name="jcr:primaryType" sv:type="Name">
      <sv:value>mgnl:tax-article</sv:value>
    </sv:property>
    <sv:property sv:name="jcr:createdBy" sv:type="String">
      <sv:value>system</sv:value>
    </sv:property>
</sv:node>
<sv:node sv:name="test2"
    xmlns:sv="http://www.jcp.org/jcr/sv/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <sv:property sv:name="jcr:primaryType" sv:type="Name">
      <sv:value>mgnl:tax-article</sv:value>
    </sv:property>
    <sv:property sv:name="jcr:createdBy" sv:type="String">
      <sv:value>admin</sv:value>
    </sv:property>
</sv:node>  
</Articles>

我尝试了以下步骤:

1. 使用 OPENROWSET 函数将 XML 文件中的 XML 数据导入到 SQL Server 表中

---

USE DataMigration

GO


CREATE TABLE ArticlesXML
(
Id INT IDENTITY PRIMARY KEY,
ArticlesXMLData XML,
LoadedDateTime DATETIME
)


INSERT INTO ArticlesXML(ArticlesXMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\Temp\articles.xml', SINGLE_BLOB) AS x;


SELECT * FROM ArticlesXML

---

1. 使用 OPENXML 函数解析 XML 数据

---

USE DataMigration

GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = ArticlesXMLData FROM ArticlesXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT ArticleName
FROM OPENXML(@hDoc, 'Articles/sv:node')
WITH 
(
   ArticleName [varchar](100) '@sv:name'
)

EXEC sp_xml_removedocument @hDoc

GO

在执行上述查询时，出现以下错误:

Msg 6603, Level 16, State 2, Line 14 XML parsing error: Reference to undeclared namespace prefix: 'sv'.

我想从 TestArticles.xml 中获取以下内容

1. 来自每个 sv:node 的 sv:name
2. sv:node 中每个 sv:property 节点的 sv:value

谁能帮我解决这个问题？

Answer 1

通过此查询，您可以读取 XML 中的所有数据:

CREATE TABLE ArticlesXML
(
Id INT IDENTITY PRIMARY KEY,
ArticlesXMLData XML,
LoadedDateTime DATETIME
)
GO

SET IDENTITY_INSERT ArticlesXML ON;
INSERT INTO ArticlesXML(Id,ArticlesXMLData,LoadedDateTime) VALUES
(1,
'<?xml version="1.0" encoding="UTF-8"?>
<Articles>
<sv:node sv:name="test1"
    xmlns:sv="http://www.jcp.org/jcr/sv/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <sv:property sv:name="jcr:primaryType" sv:type="Name">
      <sv:value>mgnl:tax-article</sv:value>
    </sv:property>
    <sv:property sv:name="jcr:createdBy" sv:type="String">
      <sv:value>system</sv:value>
    </sv:property>
</sv:node>
<sv:node sv:name="test2"
    xmlns:sv="http://www.jcp.org/jcr/sv/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <sv:property sv:name="jcr:primaryType" sv:type="Name">
      <sv:value>mgnl:tax-article</sv:value>
    </sv:property>
    <sv:property sv:name="jcr:createdBy" sv:type="String">
      <sv:value>admin</sv:value>
    </sv:property>
</sv:node>  
</Articles>',GETDATE());
SET IDENTITY_INSERT ArticlesXML OFF;

WITH XMLNAMESPACES('http://www.jcp.org/jcr/sv/1.0' AS sv
                  ,'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT Id
      ,Article.value('@sv:name','varchar(max)') AS ArticleName
      ,Property.value('@sv:name','varchar(max)') AS PropertyName
      ,Property.value('@sv:type','varchar(max)') AS PropertyType
      ,Property.value('sv:value[1]','varchar(max)') AS PropertyValue
      ,LoadedDateTime
FROM ArticlesXML
CROSS APPLY ArticlesXML.ArticlesXMLData.nodes('/Articles/sv:node') A(Article)
CROSS APPLY A.Article.nodes('sv:property') AS B(Property);
--CleanUp
--DROP TABLE ArticlesXML;

结果

+----+-------------+-----------------+--------------+------------------+-------------------------+
| Id | ArticleName | PropertyName    | PropertyType | PropertyValue    | LoadedDateTime          |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test1       | jcr:primaryType | Name         | mgnl:tax-article | 2016-03-31 13:52:26.753 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test1       | jcr:createdBy   | String       | system           | 2016-03-31 13:52:26.753 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test2       | jcr:primaryType | Name         | mgnl:tax-article | 2016-03-31 13:52:26.753 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test2       | jcr:createdBy   | String       | admin            | 2016-03-31 13:52:26.753 |
+----+-------------+-----------------+--------------+------------------+-------------------------+

如果你想以某种方式查询这个(过滤器，聚合......)你有几个机会:

• 将结果放入临时表中，然后根据需要使用它
• 与声明的表变量相同
• 包围并将其用作 CTE

(看起来像这样)

WITH XMLNAMESPACES('http://www.jcp.org/jcr/sv/1.0' AS sv
                  ,'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
,TableDataCTE AS
(
    SELECT Id
          ,Article.value('@sv:name','varchar(max)') AS ArticleName
          ,Property.value('@sv:name','varchar(max)') AS PropertyName
          ,Property.value('@sv:type','varchar(max)') AS PropertyType
          ,Property.value('sv:value[1]','varchar(max)') AS PropertyValue
          ,LoadedDateTime
    FROM ArticlesXML
    CROSS APPLY ArticlesXML.ArticlesXMLData.nodes('/Articles/sv:node') A(Article)
    CROSS APPLY A.Article.nodes('sv:property') AS B(Property)
)
SELECT * FROM TableDataCTE;
WHERE ...

• 或者您可以使用 XQuery 谓词(例如 .nodes('/Articles/sv:node[sv:name="test1"]'))

编辑增强的 SELECT 以反射(reflect)您根据您在评论中提供的示例读取多值属性的需要:

WITH XMLNAMESPACES('http://www.jcp.org/jcr/sv/1.0' AS sv
                  ,'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT Id
      ,Article.value('@sv:name','varchar(max)') AS ArticleName
      ,Property.value('@sv:name','varchar(max)') AS PropertyName
      ,Property.value('@sv:type','varchar(max)') AS PropertyType
      ,Value.value('.','varchar(max)') AS PropertyValue
      ,LoadedDateTime
FROM ArticlesXML
CROSS APPLY ArticlesXML.ArticlesXMLData.nodes('/Articles/sv:node') A(Article)
CROSS APPLY A.Article.nodes('sv:property') AS B(Property)
CROSS APPLY B.Property.nodes('sv:value') AS C(Value);

结果:

+----+-------------+-----------------+--------------+------------------+-------------------------+
| Id | ArticleName | PropertyName    | PropertyType | PropertyValue    | LoadedDateTime          |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test1       | jcr:primaryType | Name         | mgnl:tax-article | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test1       | jcr:createdBy   | String       | system           | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test2       | jcr:primaryType | Name         | mgnl:tax-article | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test2       | jcr:createdBy   | String       | admin            | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test3       | jcr:createdBy   | String       | admin1           | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test3       | jcr:createdBy   | String       | admin2           | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test3       | jcr:createdBy   | String       | admin3           | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test3       | jcr:createdBy   | String       | admin4           | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test3       | jcr:createdBy   | String       | admin5           | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
| 1  | test3       | jcr:createdBy   | String       | admin6           | 2016-03-31 20:30:27.240 |
+----+-------------+-----------------+--------------+------------------+-------------------------+