T-SQL - OPENXML

Question

I have the following SQL code:

DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(4000);
SET @XmlDocument = N'<ROOT>
<projectId>15468</projectId><projectId>15469</projectId><projectId>15472</projectId> 
<projectId>15849</projectId><projectId>13801</projectId><projectId>13802</projectId>
<projectId>13803</projectId><projectId>15684</projectId><projectId>14044</projectId> 
<projectId>15722</projectId><projectId>15753</projectId><projectId>15770</projectId> 
<projectId>15771</projectId>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, 'ROOT//', 2)
  WITH (projectId  VARCHAR(10));

If I run it like this, I only get project id into the first row, the rest are nulls. Any idea to specify the path correctly?

Thanks

Update. Actual code with nodes:

    SET NOCOUNT ON;

    create table #selectedProjectsList (projectId int)
    create index #idx_selectedProjectsList On #selectedProjectsList 
    (projectId)

    if @params is not null
    BEGIN
        if @params.value('count(//projectId)', 'int') > 0
        BEGIN
          insert into #selectedProjectsList
          select PIDS.PID.value('.', 'int')
          From @params.nodes('//projectId') as PIDS(PID)
        END
    END

params is a parameter that looks like this:

@params xml = null

Any reason to use that instead of `declare @XmlDocument xml` and `select x.x.value(N'.', 'varchar(10)') from @XmlDocument.nodes(N'/ROOT/projectId') x(x)`? — GSerg, May 15 '23 at 23:16
I am trying to replace @XmlDocument.nodes that I have currently with something else to troubleshoot the problem. It seems like the server cannot process a very long xml string. — Mark, May 15 '23 at 23:23
@Martin. Thats actually did it, thanks. This "VARCHAR(10) '.'" did it. — Mark, May 15 '23 at 23:26
If you show the actual query you are doing with `.nodes` we can try and improve it. It's very easy to end up with poor performing XML queries. — Charlieface, May 15 '23 at 23:49
OK you can optimize that quite a lot. There is no need for the extra `count` check because `.nodes` will return no rows in that case. The descendant axis `//` is slow, you should use `/ ` instead, also use `text()[1]` instead of `.`. So you need `insert into #selectedProjectsList (projectId) select PIDS.PID.value('text()[1]', 'int') from @params.nodes('/Root/projectId') as PIDS(PID)` — Charlieface, May 16 '23 at 09:19

Charlieface · Accepted Answer · 2023-05-15T23:48:15.783

2

Your primary issue was that you had the XPath wrong, it should have been ROOT/projectId. You then need to change your WITH to WITH (projectId VARCHAR(10) 'text()');. And for defensive coding you need to make sure to release the handle.

BEGIN TRY
    -- Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
    -- Execute a SELECT statement using OPENXML rowset provider.
    SELECT *
    FROM OPENXML (@DocHandle, 'ROOT/projectId', 2) 
    WITH (projectId  VARCHAR(10) 'text()');

    EXEC sp_xml_removedocument @DocHandle; 
END TRY
BEGIN CATCH
    EXEC sp_xml_removedocument @DocHandle; 
    THROW;
END CATCH;

db<>fiddle

But it's much better to use the newer (from 2005!) XML functions, such as .nodes and .value. Note that they only work on xml typed variables.

DECLARE @XmlDocument xml = N'<ROOT>
<projectId>15468</projectId><projectId>15469</projectId><projectId>15472</projectId> 
<projectId>15849</projectId><projectId>13801</projectId><projectId>13802</projectId>
<projectId>13803</projectId><projectId>15684</projectId><projectId>14044</projectId> 
<projectId>15722</projectId><projectId>15753</projectId><projectId>15770</projectId> 
<projectId>15771</projectId>
</ROOT>';

SELECT x1.prj.value('text()[1]', 'varchar(10)')
FROM @XmlDocument.nodes('ROOT/projectId') x1(prj);

db<>fiddle

edited May 15 '23 at 23:48

answered May 15 '23 at 23:14

Charlieface

52,284
6
19
43

That's what I am trying to avoid using. I have two servers with the same db. One is running it fine, the other seems like is chocking on XML processing because in reality the XNL string is over 100K. – Mark May 15 '23 at 23:17
No, ROOT/projectId is not working. All rows are nulls. – Mark May 15 '23 at 23:19
1

@Mark - You also need to change the `WITH` clause as `OPENXML (@DocHandle, 'ROOT/projectId', 2) WITH (projectId VARCHAR(10))` would match `15468` – Martin Smith May 15 '23 at 23:33
Yes, that's right. – Mark May 15 '23 at 23:38
Sounds like it might be better to use something to different to process the XML, like Python or C# or Powershell. SQL Server is not really made for processing giant XML – Charlieface May 15 '23 at 23:45

score 1 · Answer 2 · answered May 15 '23 at 23:47

The ROOT// returns 27 results.

The ROOT element, and 13 projectId elements and 13 #text nodes.

The only one of those where applying projectId in the WITH clause returns anything is when it is applied to the ROOT node.

You can use

SELECT *
FROM OPENXML (@DocHandle, 'ROOT/projectId', 2) 
WITH (projectId  VARCHAR(10) '.');

Though I'm not sure this will help or hinder performance compared to .nodes

T-SQL - OPENXML

2 Answers2