XQuery sql Join inserts empty node when join does not match-CodePudding

I am using SQL Server 2012 and am looking a way to update my xml column by looking up values from another table.

I have a the following schema:

USE tempdb;
GO

DROP TABLE IF EXISTS [dbo].[tblstepid];

CREATE TABLE [dbo].[tblstepid](
    [stepid] [uniqueidentifier]  NOT NULL,
    [name] nvarchar(32) NOT NULL
);

INSERT INTO dbo.tblstepid ([stepid], name ) VALUES ('E36A3450-1C8F-44DA-B4D0-58E5BFE2A987','step1')

INSERT INTO dbo.tblstepid ([stepid], name ) VALUES ('11D70A50-08AC-4767-A0D3-87717384FF45','step2')

DROP TABLE IF EXISTS [dbo].[tblStepList];

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

INSERT INTO dbo.tblStepList ([Data]) VALUES
(N'<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>
    <TextReadingId>12</TextReadingId>

  </Step>
</Steplist>');





    INSERT INTO dbo.tblStepList ([Data]) VALUES
(N'<Steplist>
  <Step>
    <StepId>d9e42387-56e3-40a1-9698-e89c930d98d1</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>   
    <TextReadingId>0</TextReadingId>  
  </Step>
  <Step>
    <StepId>e5eaf947-24e1-4d3b-a92a-d6a90841293b</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>')

Now I want to a update all the steps with matching step ids in the other table as follows:

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

And my result xml output has an empty node which I do not want. How can I write my join to not add empty nodes for me and also not to set the data if nothing matches which means my second record in my table xml should never be touched?

The problem I am trying to solve is that I am trying to update xml for a table with millions of records by joining from another table and I don't want the update statement to touch the data if the join does not match.

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

My expected output is as follows:

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

CodePudding user response：

Please try the following solution.

I added a couple of XQuery if/else statements to prevent the empty <stepname /> tag.

SQL

USE tempdb;
GO

-- DDL and sample data population, start
DROP TABLE IF EXISTS [dbo].[tblStepID];
DROP TABLE IF EXISTS [dbo].[tblStepList];

CREATE TABLE dbo.tblstepid(
    stepid uniqueidentifier  NOT NULL,
    [name] nvarchar(32) NOT NULL
);
INSERT INTO dbo.tblStepID (StepId, [Name]) VALUES
('e36a3450-1c8f-44da-b4d0-58e5bfe2a987', 'step1'),
('4078c1b1-71ea-4578-ba61-d2f6a5126ba1', 'step2');

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

INSERT INTO dbo.tblStepList ([Data]) VALUES
(N'<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>
    <TextReadingId>12</TextReadingId>
  </Step>
</Steplist>'),
(N'<Steplist>
  <Step>
    <StepId>d9e42387-56e3-40a1-9698-e89c930d98d1</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>   
    <TextReadingId>0</TextReadingId>  
  </Step>
  <Step>
    <StepId>e5eaf947-24e1-4d3b-a92a-d6a90841293b</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>');
-- DDL and sample data population, end

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

-- test
SELECT * FROM dbo.tblStepList;