XML文档到SQL Server查询

Question

如何从XML文件获取数据到SQL查询，下面的代码不起作用

<FVDL>
<EngineData>
<RuleInfo>
<Rule id="13EFF385-69A9-494A-9C67-951FEDAB25ED">
<MetaInfo>
<Group name="package">Python Core xml</Group>
<Group name="inputsource">XML Document</Group>
<Group name="audience">broad</Group>
</MetaInfo>
</Rule>
<Rule id="E9DB1C0E-025E-4EBF-A804-6C3DA413E652">
<MetaInfo>
<Group name="altcategoryMIS">Python Core zipfile</Group>
<Group name="altcategoryGDPR">Access Violation</Group>
</MetaInfo>
</Rule>  
</RuleInfo>
</EngineData>
</FVDL>


USE OPENXMLTesting
GO

DECLARE @XML AS XML, @hDoc AS INT

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

Select ID, name, [Group]
FROM OPENXML(@hDoc, 'FVDL/EngineData/RuleInfo') 

WITH  
(
ID [varchar](100) 'Rule/@id',
[name] [varchar](100) 'Rule/MetaInfo/Group/@name',
[Group] [varchar](1000) 'MetaInfo/Group/.. '
)  

EXEC sp_xml_removedocument @hDoc

查找这样的结果

<表类>ID名称组13EFF385-69A9-494A-9C67-951FEDAB25EDpackagePython Core xml13 eff385 - 69 - a9 - 494 - 951 - 9 - c67 fedab25ednputsourceXML文档13 eff385 - 69 - a9 - 494 - 951 - 9 - c67 fedab25ed观众广泛E9DB1C0E-025E-4EBF-A804-6C3DA413E652altcategoryMISPython Core zipfilee9db1c0e - 025 e - 4 - ebf a804 - 6 c3da413e652altcategoryGDPR访问违反

请尝试以下解决方案。

从SQL Server 2005开始，最好使用基于w3c标准的XQuery语言，同时处理XML数据类型。

Microsoft专有的OPENXML及其同伴sp_xml_preparedocument和sp_xml_removedocument被保留只是为了向后兼容过时的SQL2000服务器。他们的使用减少了，只有极少数的边缘案件。强烈建议您重新编写SQL并将其切换为XQuery。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<FVDL>
<EngineData>
<RuleInfo>
<Rule id="13EFF385-69A9-494A-9C67-951FEDAB25ED">
<MetaInfo>
<Group name="package">Python Core xml</Group>
<Group name="inputsource">XML Document</Group>
<Group name="audience">broad</Group>
</MetaInfo>
</Rule>
<Rule id="E9DB1C0E-025E-4EBF-A804-6C3DA413E652">
<MetaInfo>
<Group name="altcategoryMIS">Python Core zipfile</Group>
<Group name="altcategoryGDPR">Access Violation</Group>
</MetaInfo>
</Rule>
</RuleInfo>
</EngineData>
</FVDL>');
-- DDL and sample data population, end
SELECT p.value('@id', 'UNIQUEIDENTIFIER') AS ID
, c.value('@name', 'VARCHAR(30)')AS [Name]
, c.value('(./text())[1]', 'VARCHAR(30)')AS [Group]
FROM @tbl
CROSS APPLY xmldata.nodes('/FVDL/EngineData/RuleInfo/Rule') AS t1(p)
CROSS APPLY t1.p.nodes('MetaInfo/Group') AS t2(c);

+--------------------------------+-----------------+---------------------+
|               ID               |      Name       |       Group         |
+--------------------------------+-----------------+---------------------+
| 13EFF385-69A9-494A-9C67-951FED | package         | Python Core xml     |
| 13EFF385-69A9-494A-9C67-951FED | inputsource     | XML Document        |
| 13EFF385-69A9-494A-9C67-951FED | audience        | broad               |
| E9DB1C0E-025E-4EBF-A804-6C3DA4 | altcategoryMIS  | Python Core zipfile |
| E9DB1C0E-025E-4EBF-A804-6C3DA4 | altcategoryGDPR | Access Violation    |
+--------------------------------+-----------------+---------------------+

OPENXML有点不稳定，已经过时了。如果需要使用它，则需要提供到<Group>的路径，然后再返回到其他元素。这应该可以根据您的示例XML为您完成。

DECLARE @XML XML = N'<FVDL>
<EngineData>
<RuleInfo>
<Rule id="13EFF385-69A9-494A-9C67-951FEDAB25ED">
<MetaInfo>
<Group name="package">Python Core xml</Group>
<Group name="inputsource">XML Document</Group>
<Group name="audience">broad</Group>
</MetaInfo>
</Rule>
<Rule id="E9DB1C0E-025E-4EBF-A804-6C3DA413E652">
<MetaInfo>
<Group name="altcategoryMIS">Python Core zipfile</Group>
<Group name="altcategoryGDPR">Access Violation</Group>
</MetaInfo>
</Rule>  
</RuleInfo>
</EngineData>
</FVDL>'

DECLARE @hDoc AS INT

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

Select ID, name, [Group]
FROM OPENXML(@hDoc, 'FVDL/EngineData/RuleInfo/Rule/MetaInfo/Group') 
WITH  
(
id [varchar](100) '../../@id',
[name] [varchar](100) '@name',
[Group] [varchar](1000) '.'
)  

EXEC sp_xml_removedocument @hDoc

我强烈建议你不要使用OPENXML，因为它有很多问题。

使用更新的XQuery函数，它们使用起来要简单得多

• 注意一个.nodes如何馈送到下一个。这是必要的，因为有两个独立的节点级别需要分解成单独的行。

SELECT
ID = rl.value('@id','uniqueidentifier'),
[name] = grp.value('@name', 'varchar(100)'),
[Group] = grp.value('text()[1]','varchar(1000)')
FROM XMLwithOpenXML
CROSS APPLY XMLData.nodes('FVDL/EngineData/RuleInfo/Rule') x1(rl)
CROSS APPLY x1.rl.nodes('MetaInfo/Group') x2(Grp);

,db&lt的在小提琴