CODEWITHSUNDEEP
sql-serverxmlopenxmlsql-server-openxml
chaika_sv
chaika_sv

Reputation: 413

MS SQL Server - OpenXML - Multiple elements

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?

Upvotes: 2

Views: 879

Answers (1)

PSK
PSK

Reputation: 17943

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.

Upvotes: 3

Related Questions