Openxml with namespace

Question

Can someone please help extract this:

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

I use:

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

I don't get anything unless I remove the namespace from xml

Answer 1

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