XQuery Replace

BabyAqua · August 12, 2019, 6:32am

I have gotten this to work but not exactly what I want yet. Rather than hard coding a "[1]" to replace the value, I would like to replace when the following path meets this logic:
/ParameterValues/ParameterValue/Name/text() = "To"

and this path in the same node level gets updated with a value:
/ParameterValues/ParameterValue/Value/text())

DECLARE @My_XML AS XML = 
'
<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>TestingToEmail@bogus.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>TestingCCEmail@bogus.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeReport</Name>
    <Value>False</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Subject</Name>
    <Value>@ReportName was executed at @ExecutionTime</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeLink</Name>
    <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Priority</Name>
    <Value>NORMAL</Value>
  </ParameterValue>
</ParameterValues>
'

DROP TABLE IF EXISTS #TempXML
CREATE TABLE #TempXML (ID INT IDENTITY, My_XML XML)

INSERT #TempXML VALUES(@My_XML)

--Replacing Values
UPDATE #TempXML
SET My_XML.modify('replace value of 
	(/ParameterValues/ParameterValue/Value/text())[1] with ("MyNewEmail@Real.com")'
)

SELECT * FROM #TempXML

BabyAqua · August 12, 2019, 6:38am

I was even able to query correctly but just need to build out the node path and inject it into the modify statement.

SELECT
	  RTRIM(LTRIM(x.Rec.query('./Name').value('.', 'VARCHAR(MAX)')		)) AS 'Field_Type'
	, RTRIM(LTRIM(x.Rec.query('./Value').value('.', 'VARCHAR(MAX)')		)) AS 'Email_Address'
FROM #TempXML aT1
CROSS APPLY aT1.My_XML.nodes('/ParameterValues/ParameterValue') AS x(Rec)
WHERE RTRIM(LTRIM(x.Rec.query('./Name').value('.', 'VARCHAR(MAX)')		)) IN ('TO', 'CC', 'BCC')

yosiasz · August 12, 2019, 1:04pm

What are you using this xml for? Are you using it as a parameter payload?

I think it is time for you to start looking into tvp table value parameters using table values types

BabyAqua · August 12, 2019, 3:30pm

That XML belongs to the subscription table for the report server. I was asked to automate emails by inserting/deleting for the subscription.

yosiasz · August 12, 2019, 4:24pm

disclaimer! I would not recommend this but it is up to you.

use sqlteam
go

DECLARE @My_XML AS XML = 
'
<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>BabyAqua@bogus.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>TestingCCEmail@bogus.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeReport</Name>
    <Value>False</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Subject</Name>
    <Value>@ReportName was executed at @ExecutionTime</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeLink</Name>
    <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Priority</Name>
    <Value>NORMAL</Value>
  </ParameterValue>
</ParameterValues>
'



CREATE TABLE #TempXML (ID INT IDENTITY, My_XML XML)
INSERT #TempXML VALUES(@My_XML)

create table #updateemails(Name nvarchar(max), Value nvarchar(max))

insert into #updateemails
SELECT RTRIM(LTRIM(x.Rec.query('./Name').value('.', 'VARCHAR(MAX)')	)) AS 'Name'
	,  RTRIM(LTRIM(x.Rec.query('./Value').value('.', 'VARCHAR(MAX)'))) AS 'Value'
FROM #TempXML aT1
CROSS APPLY aT1.My_XML.nodes('/ParameterValues/ParameterValue') AS x(Rec)

update tgt
   set tgt.Value = 'BabyAqua@waterquality.com'
  from #updateemails tgt
  where Name = 'TO'


 select * 
   from #updateemails
   FOR XML PATH ('ParameterValue'), root ('ParameterValues');  

--then update the subscriptions table using 
--#updateemails table, make sure to add the 
--SubscriptionID column 2828B2E5-EA97-4D49-AA5F-16A6EB0C98CC. 
--I DO NOT RECOMMEND THIS!
drop table #updateemails

DROP TABLE #TempXML

BabyAqua · August 12, 2019, 5:30pm

I found a different method of doing it with Execute and directly modify the XML. But I must say, I like the way your method works better. Much cleaner. Thanks

yosiasz · August 13, 2019, 12:47am

yes but I would not recommend it. I know you work with medical related stuff if I remember correctly (HIPPA) and if someone mistakenly changes the TO it will update the subscription table with no sort of validation from concerned parties. You could mistakenly send out sensitive data and get sued and/or fired! I don't see any sort of check and balance or any sort of validation, not sure if there is something upstream such as the application that sends the new data to the subscription is locked down to select users.

BabyAqua · August 13, 2019, 5:05pm

I appreciate your concern but we do it as a practice where we do not send out attachments of the reports and only send out links. In order to get to the links, the receivers would have to be on the Active Directory. You may not have noticed but the XML that you helped modify also have those fields to disable attachment and provide links only.

Thanks

BabyAqua · August 13, 2019, 5:18pm

I do have another question for future knowledge and derive of complexity. The original XML was a 1 level nested XML document.

In the case of a multi level nested XML document, how should I pull it out to a table; UPDATE; and then do a FOR XML PATH?

Thanks

<Root>
  <Level1>
    <GroupName>TestName1</GroupName>
    <GroupID>TestGroupID1</GroupID>
    <Level2>
      <Level3>
        <Name>Customer 1</Name>
        <Description>My Description 1</Description>
      </Level3>
    </Level2>
  </Level1>
  <Level1>
    <GroupName>TestName2</GroupName>
    <GroupID>TestGroupID2</GroupID>
    <Level2>
      <Level3>
        <Name>Customer 2</Name>
        <Description>My Description 2</Description>
      </Level3>
    </Level2>
  </Level1>

BabyAqua · August 13, 2019, 5:29pm

On my own attempt, I was able to pull all the data out to a table which works. But now, how do I put it back to XML with FOR XML PATH?

My_Index	GroupName	GroupID	Name	Description
1	TestName1	TestGroupID1	Customer 1	My Description 1
2	TestName2	TestGroupID2	Customer 2	My Description 2

yosiasz · August 13, 2019, 5:30pm

yep but you need to specify the path

<root>
   <baby>Tooth Ache</baby>
</root>

BabyAqua · August 13, 2019, 5:56pm

Thanks for the lesson in FOR XML PATH. I was able to generate the XML in two different ways. I like this method because the concept is dump everything to the table; modify, rebuild the XML and replace the XML.

If I was to do the other method, I would have to loop through. I wonder if anyone on here has extensive knowledge with the other method using MODIFY and able to achieve the same result for this nested XML example.

--PURPOSE: Rebuild the XML
SELECT
	  GroupName	
	, GroupID	
	, [Name]			AS 'Level2/Level3/Name'
	, Description		AS 'Level2/Level3/Description'
FROM #TEMPTable_Split_Value
FOR XML PATH ('Level1'), root ('Root')



--PURPOSE: Rebuild the XML
SELECT
	  GroupName	
	, GroupID	

    , (
		SELECT 
          [Name]				AS 'Level3/Name' 
		, Description			AS 'Level3/Description'
		FROM #TEMPTable_Split_Value aTI1
        WHERE aTI1.My_Index = aTO1.My_Index
        FOR XML PATH('Level2'), TYPE
		)
FROM #TEMPTable_Split_Value aTO1
FOR XML PATH ('Level1'), root ('Root')

BabyAqua · August 13, 2019, 7:15pm

My fields "Name" and "Description" are getting concatenated on records. How would I keep them apart? I tried to do a Cross Join.

DECLARE @My_XML AS XML = 
'
<Root>
  <Level1>
    <GroupName>TestName1</GroupName>
    <GroupID>TestGroupID1</GroupID>
    <Level2>
      <Level3>
        <Name>Customer 1a</Name>
        <Description>My Description 1a</Description>
      </Level3>
      <Level3>
        <Name>Customer 1b</Name>
        <Description>My Description 1b</Description>
      </Level3>
    </Level2>
  </Level1>
  <Level1>
    <GroupName>TestName2</GroupName>
    <GroupID>TestGroupID2</GroupID>
    <Level2>
      <Level3>
        <Name>Customer 2</Name>
        <Description>My Description 2</Description>
      </Level3>
    </Level2>
  </Level1>
</Root>
'

SELECT
	  ROW_NUMBER() OVER(ORDER BY Level1.Rec)																	AS My_Index
	, RTRIM(LTRIM(Level1.Rec.query('./GroupName').value('.',						'NVARCHAR(MAX)')		))	AS GroupName
	, RTRIM(LTRIM(Level1.Rec.query('./GroupID').value('.',							'NVARCHAR(MAX)')		))	AS GroupID

	, RTRIM(LTRIM(Level1.Rec.query('./Level2/Level3/Name').value('.',				'NVARCHAR(MAX)')		))	AS [Name]
	, RTRIM(LTRIM(Level1.Rec.query('./Level2/Level3/Description').value('.',		'NVARCHAR(MAX)')		))	AS Description

	--, RTRIM(LTRIM(Level2.Rec.query('./Name').value('.',				'NVARCHAR(MAX)')		))	AS [Name]
	--, RTRIM(LTRIM(Level2.Rec.query('./Description').value('.',		'NVARCHAR(MAX)')		))	AS Description
FROM @My_XML.nodes('/Root/Level1') AS Level1(Rec)
--CROSS APPLY Level1.Rec.nodes('/Level2/Level3') AS Level2(Rec)

yosiasz · August 13, 2019, 7:24pm

post a picture of the issue or something what do you mean ?

BabyAqua · August 13, 2019, 7:34pm

If I run the code that I just provided, I get the following:

See the field "Name" for record 1? It is combined together and I am trying to keep it apart like in the XML. So the result would look more like this:

BabyAqua · August 13, 2019, 7:42pm

Found the answer. I had to drop the "/" and it worked.

FROM @My_XML.nodes('Root/Level1')						AS Level1(Rec)
CROSS APPLY Level1.Rec.nodes('Level2/Level3')			AS Level2(Rec)

yosiasz · August 13, 2019, 7:49pm

and you need the cross apply. but I don't think it works yet?

Also check this out. You can walk back the xml tree like a document folder

SELECT
    X.rail.query('Name').value('.','VARCHAR(max)') AS 'Name',
	X.rail.query('../../GroupName').value('.',						'NVARCHAR(MAX)')
FROM @Doc.nodes('Root/Level1/Level2/Level3') AS X(rail)

BabyAqua · August 14, 2019, 2:26am

I was able to transform the XML with OpenXML but according with this article, it is better to use the node method: