logo标签列表

在尝试读取XML节点值时发生XML解析错误

sql-serverxmlsql-server-2014
3

我有一个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

使用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解析错误：未声明命名空间前缀'sv'。

我想从TestArticles.xml中获取以下内容：
  1. 每个sv:node中的sv:name
  2. 每个sv:property节点中的sv:value
请问有谁能帮助我解决这个问题吗？
йЎәдҫҝжҸҗдёҖдёӢпјҡдҪҝз”ЁFROM OPENXMLе’Ңзӣёеә”зҡ„еӯҳеӮЁиҝҮзЁӢе·Із»ҸиҝҮж—¶дәҶпјҢдёҚеә”еҶҚдҪҝз”Ё... - Shnugo
2个回答
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以反映您根据评论中给出的示例需要读取多值属性的需求：

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 |
+----+-------------+-----------------+--------------+------------------+-------------------------+
非常感谢您的帮助。它确实对我有帮助 :). 我还需要您的另外一个小帮助。如果在这个模式中有一个节点：<sv：property sv：name="jcr:createdBy" sv：type="String"> <sv：value>admin1</sv：value> <sv：value>admin2</sv：value> <sv：value>admin3</sv：value> <sv：value>admin4</sv：value> <sv：value>admin5</sv：value> <sv：value>admin6</sv：value> </sv：property>，那么在这种情况下，对于我来说，Property.value('sv:value[1]','varchar(max)') AS PropertyValue无法正常工作。 - santosh kumar patro
简而言之，我需要所有的值：admin1、admin2、admin3、admin4、admin5。SELECT Id, 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); 上述查询在这里无法工作。请帮忙解决。 - santosh kumar patro
超酷！你太棒了:）。非常感谢你的帮助，这真的帮了我解决了我的问题：）。再次感谢你的帮助。 - santosh kumar patro
0

你需要明确地先定义前缀，例如使用 WITH XMLNAMESPACES 和 XQuery：

;WITH XMLNAMESPACES('http://www.jcp.org/jcr/sv/1.0' as sv)
SELECT c.value('@sv:name', 'varchar(100)') As Name
FROM ArticlesXML a
    CROSS APPLY ArticlesXMLData.nodes('Articles/sv:node') t(c)
非常感谢您的回复。但是在这里，我想根据where条件中的sv:name来读取sv:property标签内的sv:value节点的值。例如，当sv:name="jcr:uuid"时，sv:value=3fe558bd-d3aa-4c27-a975-f5ee8abc497f。 - santosh kumar patro
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接