Using FLWOR to get data from another table

Question

I have a table in SQL Server 2012 with this structure:

CREATE TABLE [dbo].[tblStepList]
(
    [ToDoId] [int] IDENTITY(1,1) NOT NULL,
    [Data] [xml] NOT NULL
)

And the Data column is of type XML and contents like this:

<Steplist>
  <Step>
    <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>     
  </Step>
  <Step>
    <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>'

I want to update each row of the table with my new xml to look with new node named TextReadingId after TextReading name

I have a secondary table with text reading values stepid and textreadingid

StepId                                TextReadingId 
---------------------------------------------------
e36a3450-1c8f-44da-b4d0-58e5bfe2a987    118
4078c1b1-71ea-4578-ba61-d2f6a5126ba1    119
d466a8ee-9214-4b9c-94f9-2117f5dffe93    401

And I want my TextReadingId values to come from the table above

 <Steplist>
          <Step>
            <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
            <Rank>1</Rank>
            <IsComplete>false</IsComplete>
            <TextReadingName>bug-8588_Updated3</TextReadingName>    
          <TextReadingId>118</TextReadingId>   
          </Step>
          <Step>
            <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
            <Rank>2</Rank>
            <TextReadingName>reading1</TextReadingName>
          <TextReadingId>401</TextReadingId> 
          </Step>
        </Steplist>';

This is what I tried but it is not working as expected

DECLARE @i int;

SELECT
    @i = s.data.value('count(/Steplist/Step)', 'nvarchar(max)')
FROM 
    tblStepList s

SET data.modify('insert <TextReadingId>{sql:variable("@i")}</TextReadingId> as last into (/Steplist/Step[sql:variable("@i")])[1]')

PRINT @i

Here is another answer that helped but it does not join with my other table for the results

Charlieface · Accepted Answer · 2021-10-26T23:59:08.520

2

The easiest way to do this is to rebuild each Step node and then aggregate it back up using FOR XML

Within the correlated subquery, we do the following:

Break out the Step nodes using .nodes()
Left join StepReading on the StepId node value
Create an unnamed column containing a new Step node...
... which contains all the children from the existing one using ./* ...
... and an extra child node TextReadingId with the value from StepReading
Then aggregate back up using FOR XML

UPDATE sl
SET Data = (
    SELECT v.Step.query('
<Step>{./*,
        if (not(./TextReadingId)) then
            <TextReadingId>{sql:column("sr.TextReadingId")}</TextReadingId>
        else ()
        }
</Step>
    ')
    FROM sl.Data.nodes('/Steplist/Step') v(Step)
    LEFT JOIN StepReading sr ON sr.StepId = v.Step.value('(StepId/text())[1]','uniqueidentifier')
    FOR XML PATH(''), ROOT('Steplist'), TYPE
)
FROM tblStepList sl;

SQL Fiddle

edited Oct 26 '21 at 23:59

answered Oct 26 '21 at 20:01

Charlieface

52,284
6
19
43

Good answer, +1 from my side! – Yitzhak Khabinsky Oct 26 '21 at 20:28
@Charlieface How can I make sure this can be rerun and does not insert duplicate TextReadingId nodes and also the TextReadingId is inserted directly after textReadingname node if it exists? – Helen Araya Oct 26 '21 at 23:52
1

OK have modified for you – Charlieface Oct 26 '21 at 23:59
@Charlieface Thanks a lot. Can I make sure it is after textReadingname node if it exists? – Helen Araya Oct 27 '21 at 00:04
1

It will always place this new node last, and the new edit will only insert if it's not there already – Charlieface Oct 27 '21 at 09:42
@Charlieface I have almost a million records in my db and this is taking so slow. Is there a way to optimize your query? – Helen Araya Nov 01 '21 at 15:11
Please share a query plan via https://brentozar.com/pastetheplan. Yous should have an index on `StepReading (StepId) INLCUDE (TextReadingId)` or similar. Adding `WHERE sl.Data.exist('/Steplist/Step/TextReadingId') = 0` at the end, and removing the `if(not` may also speed things up, see fiddle http://sqlfiddle.com/#!18/fbb25/22 – Charlieface Nov 01 '21 at 23:13
@Charlieface I just did share it . FYI I use CTE for StepReading – Helen Araya Nov 03 '21 at 20:39
@Charlieface https://www.brentozar.com/pastetheplan/?id=BkewQdlwK – Helen Araya Nov 03 '21 at 22:20
Change the XQuery at the top to use `text()` and more sensible data types `x.XmlCol.value('(StepId/text())[1]', uniqueidentifier') as StepId, x.XmlCol.value('(TextReadingName/text())[1]', 'int') as [TextReadingName]`. Dump the whole of the CTE `tblWOInstructionsteptextreading` into a well-indexed temp table. Then join on that. – Charlieface Nov 03 '21 at 22:30
@Charlieface Can you check https://stackoverflow.com/questions/69843694/xquery-sql-select-node-only-if-exists – Helen Araya Nov 04 '21 at 17:52
The answer there looks OK – Charlieface Nov 04 '21 at 20:12
@Charlieface check this https://stackoverflow.com/q/69854955/3038042 – Helen Araya Nov 05 '21 at 15:04

Using FLWOR to get data from another table

1 Answers1

Linked