How can I make this SQL function that parses XML faster

I am having a tough time finding a way to improve this custom SQL script to an Umbraco database.

Here is the sql script to get and parse the xml and select the correct value:

DECLARE @Title nvarchar(1000)
SET @Title =
(SELECT @NodeXml.query('//TechnicalData/title' ).value('.', 'nvarchar(max)') Title
FROM cmsContentXml xt JOIN umbracoNode un ON un.id = xt.NodeId
WHERE xt.nodeId = @NodeId)

SELECT iif(CHARINDEX('"values":null', @Title,0) > 0,'',Substring(@Title, 21, CHARINDEX('}', @Title) - 22)) Title

The partial XML is as follows:

<?xml version="1.0"?>
<TechnicalData id="1261" key="b6a2f67a-0f0f-40dd-a3c6-c5fb9d8b78b9" parentID="1092" level="3" creatorID="0" sortOrder="12" createDate="2016-07-14T13:56:37" updateDate="2016-07-15T12:06:25" nodeName="Rioolkolken Type 1 Drainerend via de zijwanden" urlName="rioolkolken-type-1-drainerend-via-de-zijwanden" path="-1,1089,1092,1261" isDoc="" nodeType="1154" creatorName="admin" writerName="Karl" writerID="1" template="1085" nodeTypeAlias="TechnicalData">
    <title>{"values":{"nl-BE":"Rioolkolken Type 1 Drainerend via de zijwanden"},"dtdGuid":"f2dfa88e-63b9-4913-80cd-64d770cef90e"}</title>
</TechnicalData>

This is only one small part of the script. TechnicalData contains around 20 nodes, which all have to be parsed. The full script can be found here

The reason for this custom script is because I needed to extend the Umbraco site with an ssrs report.

Solution

Your approach is slow, because you take one single bit of information out of your XML over and over again.

With this you would get all data typesafe out of your XML in one sinlge go and without any string parsing.

DECLARE @NodeXml XML=
N'<?xml version="1.0"?>
<TechnicalData id="1261" key="b6a2f67a-0f0f-40dd-a3c6-c5fb9d8b78b9" parentID="1092" level="3" creatorID="0" sortOrder="12" createDate="2016-07-14T13:56:37" updateDate="2016-07-15T12:06:25" 
nodeName="Rioolkolken Type 1 Drainerend via de zijwanden" urlName="rioolkolken-type-1-drainerend-via-de-zijwanden" path="-1,1089,1092,1261" isDoc="" nodeType="1154" creatorName="admin" writerName="Karl" writerID="1" template="1085" nodeTypeAlias="TechnicalData">
    <title>{"values":{"nl-BE":"Rioolkolken Type 1 Drainerend via de zijwanden"},"dtdGuid":"f2dfa88e-63b9-4913-80cd-64d770cef90e"}</title>
</TechnicalData>';

SELECT td.value('@id','int') AS id
      ,td.value('@key','uniqueidentifier') AS [key]
      ,td.value('@parentID','int') AS parentID
      ,td.value('@level','int') AS [level]
      ,td.value('@creatorID','int') AS creatorID
      ,td.value('@sortOrder','int') AS sortOrder
      ,td.value('@createDate','datetime') AS createDate
      ,td.value('@updateDate','datetime') AS updateDate
      ,td.value('@nodeName','nvarchar(max)') AS nodeName
      ,td.value('@urlName','nvarchar(max)') AS urlName
      ,td.value('@path','nvarchar(max)') AS [path]
      ,td.value('@isDoc','nvarchar(max)') AS isDoc
      ,td.value('@nodeType','int') AS nodeType
      ,td.value('@creatorName','nvarchar(max)') AS creatorName
      ,td.value('@writerName','nvarchar(max)') AS writerName
      ,td.value('@writerID','int') AS writerID
      ,td.value('@template','int') AS template
      ,td.value('@nodeTypeAlias','nvarchar(max)') AS nodeTypeAlias

      ,td.value('title[1]','nvarchar(max)') AS Title

FROM @NodeXml.nodes('TechnicalData') AS A(td)

The result in this case was one single row with all your data in columns.

I doubt, that you need your values in declared variables, better use the above as CTE (set-based / inline / ad-hoc is in most cases better than procedural) and use the values from there. But: It was easy to fill the gathered information into declared variables as well (if really needed). In this case just declare your variables with correct types and use the syntax

SELECT @MyVariable=td.value('@id','int') 
      ,@OtherVariable= ...
... same for the rest...