Try this instead

    CROSS APPLY sx.XMLDoc.nodes ('root/Header') C(x)
    CROSS APPLY sx.XMLDoc.nodes ('root/PurchaseOrder') d(y)
    CROSS APPLY y.nodes ('PurchaseOrderLine') e(z)
CROSS...

• This reply was modified 1 year, 4 months ago by Phil Parkin.

See whether this helps.

DROP TABLE IF EXISTS #SomeXML;

CREATE TABLE #SomeXML
(
    XMLDoc XML
);

INSERT #SomeXML
(
    XMLDoc
)
VALUES
('<root>
  <Header>
    <xxxxxName>yyyyy</xxxxxName>
 ...

You should consider heading over to https://www.brentozar.com/pastetheplan/instructions/ and adding your actual execution plan there.

It would also be helpful if you would include the full DDL for the tables involved. Include...

I don't have time to dig into this, but there is an error in the query.

LEN('L') will always equal 1, because that's how long 'L' is.

You should also remove the...

• This reply was modified 1 year, 4 months ago by Phil Parkin.

If you want a coded solution, please provide a (sanitised as needed) version of the XML doc, not an image!

• This reply was modified 1 year, 4 months ago by Phil Parkin.

Requirements are fairly loose Eirkur. Investigate the storing of Excel documents either in Sql Server or a fileshare (on a different server to the Sql one), whichever is best....

Folders in Workspaces is now in Preview.

No need for SSAS, now that there are semantic models.

IMO, migrating to Power BI 'Tabular Reports' from SSRS is super simple and worth investigating.

DF does not replace SSIS, however. We have tried to migrate and failed (and gone back...

Did you try BISM Normalizer? Looks like the schema compare...

Are you looking to store the entire file as a single BLOB, or import its contents as individual columns?

You are correct in thinking that you need the TrustServerCertificate=true bit in your connection string. The latest version of SQL Server depends on it, unless you have a server cert...

It really depends on the data.

Try injecting ''00X11' into your version. It removes the 'X', which is not one of the specified requirements. I'm all for elegant code, and maybe...

The proposed solutions seem to be a bit more code than is needed:

SELECT *
     , ActualID = substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
 ...

DROP TABLE IF EXISTS #SomeString;

CREATE TABLE #SomeString
(
    Str1 VARCHAR(25)
);

INSERT #SomeString
(
    Str1
)
VALUES
('D00241')
,('00242 ')
,('Chicken Tikka Masala');

SELECT ss.Str1
      ,CASE
...

Here is another version of your query which looks a bit tidier (as it does not rely on DISTINCT to remove dupes). But I'm not sure it will perform any...