MS SQL Server - OpenXML - Multiple elements

Question

XML example:

<POLICY>
    <RISKS>
        <RISK>
            <DRV>1</DRV>
        </RISK>
        <RISK>
            <DRV>2</DRV>
        </RISK>
    </RISKS>
</POLICY>

I want to select both Risk elements with this query:

SELECT RISK
FROM OPENXML(@hDOC, 'POLICY/RISKS', 2)
WITH(
    RISK            XML     'RISK'
) AS Z

Expected:

1. <RISK><DRV>1</DRV></RISK>
2. <RISK><DRV>2</DRV></RISK>

Result:

1. <RISK><DRV>1</DRV></RISK>

(only first element was returned)

For comparison this query returns two rows as expected:

SELECT DRV
FROM OPENXML(@hDOC, 'POLICY/RISKS/RISK', 2)
WITH(
    DRV         XML     'DRV'
) AS Z

Result:

1. <DRV>1</DRV>
2. <DRV>2</DRV>

So the question is how can I get two Risk-rows?

Answer 1

Why are you not using the native XQuery support provided by SQL Server. OpenXML is old and having lot of issues.

You can write your query like following using XQuery Support

DECLARE @hDOC xml   
SET @hDOC='<POLICY>
    <RISKS>
        <RISK>
            <DRV>1</DRV>
        </RISK>
        <RISK>
            <DRV>2</DRV>
        </RISK>
    </RISKS>
</POLICY>'  
SELECT T.c.query('.') AS result  
FROM   @hDOC.nodes('/POLICY/RISKS/RISK') T(c)  
GO 

You will get output as

1. <RISK><DRV>1</DRV></RISK>
2. <RISK><DRV>2</DRV></RISK>

Edit: If you still want to do with OpenXml, use query like following.

DECLARE @DocHandle int 
DECLARE @hDOC VARCHAR(1000)   
SET @hDOC=N'<POLICY>
    <RISKS>
        <RISK>
            <DRV>1</DRV>
        </RISK>
        <RISK>
            <DRV>2</DRV>
        </RISK>
    </RISKS>
</POLICY>'  
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @hDOC 
SELECT RISK
FROM OPENXML(@DocHandle, 'POLICY/RISKS/RISK', 2)
WITH(
    RISK XML     '.'
) AS Z

EXEC sp_xml_removedocument @DocHandle  

You will get the desired output.