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?

PSK · Accepted Answer · 2018-02-05 09:39:02Z

3

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.

edited Feb 5, 2018 at 9:39

answered Feb 5, 2018 at 9:07

PSK

18k5 gold badges35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chaika_sv Over a year ago

Thank you for your reply. I have a lot of existed code in the project implemented with OpenXML and I just don't want to implement this part with XQuery.

Collectives™ on Stack Overflow

MS SQL Server - OpenXML - Multiple elements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related