sql - 具有 namespace 的openxml

有人能帮我提取这个吗：

<Customize xmlns="http://utsavfashion.com/web/schemas">
    <customize_details>
        <entityid>876</entityid>
    </customize_details>
</Customize>

我正在使用：

DECLARE @xml_hndl INT
EXEC sp_xml_preparedocument @xml_hndl OUTPUT, @str

SELECT *
FROM OPENXML(@xml_hndl, '/Customize/customize_details', 2)
WITH (entityid INT)

EXEC sp_xml_removedocument @xml_hndl

除非从xml中移除名称空间，否则我什么也得不到

最佳答案

XQuery：

DECLARE @x XML = '
<Customize xmlns="http://utsavfashion.com/web/schemas">
    <customize_details>
        <entityid>876</entityid>
    </customize_details>
</Customize>'

SELECT t.c.value('.', 'INT')
FROM @x.nodes('*:Customize/*:customize_details/*:entityid') t(c)

OpenXML：

DECLARE @x XML = '
<Customize xmlns="http://utsavfashion.com/web/schemas">
    <customize_details>
        <entityid>876</entityid>
    </customize_details>
</Customize>'

DECLARE @doc INT, @xmlns VARCHAR(100)

SET @xmlns = '<root xmlns:h="http://utsavfashion.com/web/schemas" />'
EXEC sp_xml_preparedocument @doc OUTPUT, @x, @xmlns

SELECT *
FROM OPENXML(@doc, '//h:Customize/h:customize_details')
    WITH (
        entityid INT '.'
    )

EXEC sp_xml_removedocument @doc