时间戳

首页 > 解决方案 > 将具有多个命名空间的 XML 解析为 SQL 的问题

sql - 将具有多个命名空间的 XML 解析为 SQL 的问题

问题描述

我在将信息从 XML 解析为具有双命名空间的 SQL 时遇到问题。看看这段代码：

DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example


SET @Xml = N'
<SiBikNet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://www.ws.bik.pl/ws/ki/2v2/types">
 <BIK_REQUEST>
    <siBikNetResponse>
        <consentDate>2018-07-29</consentDate>
        <citizenshipStatus>citizen</citizenshipStatus>
        <nationality>PL</nationality>
        <pesel>123</pesel>
    </siBikNetResponse>
 </BIK_REQUEST>
</SiBikNet>';


EXEC sys.sp_xml_preparedocument @Handle OUTPUT , @Xml, N'<SiBikNet xmlns:t="https://www.ws.bik.pl/ws/ki/2v2/types"/>'; --Prepare a parsed document 

SELECT *
FROM
       OPENXML(@Handle,'/t:SiBikNet/t:BIK_REQUEST/t:siBikNetResponse', 2)
           WITH (   nationality NVARCHAR(10) 't:nationality',
                    pesel  NVARCHAR(10)      't:pesel '
                );

EXEC sys.sp_xml_removedocument @Handle;

这为我提供了具有 2 列的表格形式的正确输出。但是当我将添加一行带有双命名空间时：然后我无法解析这些信息：

DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example


SET @Xml = N'
<SiBikNet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://www.ws.bik.pl/ws/ki/2v2/types">
 <BIK_REQUEST xmlns="">
    <siBikNetResponse>
        <consentDate>2018-07-29</consentDate>
        <citizenshipStatus>citizen</citizenshipStatus>
        <nationality>PL</nationality>
        <pesel>123</pesel>
    </siBikNetResponse>
 </BIK_REQUEST>
</SiBikNet>';


EXEC sys.sp_xml_preparedocument @Handle OUTPUT , @Xml, N'<SiBikNet xmlns:t="https://www.ws.bik.pl/ws/ki/2v2/types"/>'; --Prepare a parsed document 

SELECT *
FROM
       OPENXML(@Handle,'/t:SiBikNet/t:BIK_REQUEST/t:siBikNetResponse', 2)
           WITH (   nationality NVARCHAR(10) 't:nationality',
                    pesel  NVARCHAR(10)      't:pesel '
                );

EXEC sys.sp_xml_removedocument @Handle;

任何人都可以帮忙吗？

标签： sqlxmlopenxml

解决方案

当我遇到匿名命名空间或奇怪的命名空间组合时，最简单的方法就是使用 XPath 函数local-name()。像这样：

DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example


SET @Xml = N'
<SiBikNet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://www.ws.bik.pl/ws/ki/2v2/types">
 <BIK_REQUEST xmlns="">
    <siBikNetResponse>
        <consentDate>2018-07-29</consentDate>
        <citizenshipStatus>citizen</citizenshipStatus>
        <nationality>PL</nationality>
        <pesel>98070902702</pesel>
    </siBikNetResponse>
 </BIK_REQUEST>
</SiBikNet>';


EXEC sys.sp_xml_preparedocument @Handle OUTPUT , @Xml, N'<SiBikNet xmlns:t="https://www.ws.bik.pl/ws/ki/2v2/types"/>'; --Prepare a parsed document 

SELECT *
FROM
       OPENXML(@Handle,'/*[local-name()="SiBikNet"]/*[local-name()="BIK_REQUEST"]/*[local-name()="siBikNetResponse"]', 2)
           WITH (   nationality NVARCHAR(10) ,--'t:nationality',
                    pesel  NVARCHAR(10)      --'t:pesel '
                );

EXEC sys.sp_xml_removedocument @Handle;

推荐阅读