CODEWITHSUNDEEP
sqlxmlxpath
JohnPete22
JohnPete22

Reputation: 523

UPDATE SELECT statement with XML field

I'm attempting to do an UPDATE statement where I need to SELECT a value from a table I join on. The value I need to grab is part of an XML node, but I'm having difficulty getting it. I want to grab the LocationID from the below-mentioned XML. I edited the code and table names/fields, but the premise remains the same.

My current SQL query:

UPDATE table1

SET LocationId = CAST(t2.CustomProperties AS xml).value('(/ArrayOfCustomProperty/CustomProperty/@LocationId)[1]', 'varchar(36)')

FROM table1 as t1
INNER JOIN table2 as t2
ON t1.x = t2.x

<?xml version="1.0" encoding="utf-16"?>  <ArrayOfCustomProperty xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>ShipToId</Key>      <Value>X</Value>    </CustomProperty>    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>CustomerId</Key>      <Value>X</Value>    </CustomProperty>    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>CorporateId</Key>      <Value>X</Value>    </CustomProperty>    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>NeedsApproval</Key>      <Value>0</Value>    </CustomProperty>    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>LocationId</Key>      <Value>X</Value>    </CustomProperty>  </ArrayOfCustomProperty>

I keep getting a NULL value returned, but I can see that the LocationID does have a value.

EDIT: My Solution

CAST(CAST(CustomProperties AS xml).query('ArrayOfCustomProperty/CustomProperty/Key[text()="LocationId"]/../Value/text()') AS nvarchar)

Upvotes: 1

Views: 92

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Your XML seems to be a typical list of key-value-pairs.

If this is under your control, you really should store this as native XML... storing XML in strings is erronous and slow...

You can use a query like this to filter the needed value:

DECLARE @DeveloperId VARCHAR(10)='X';
DECLARE @Key VARCHAR(50)='LocationId';

SELECT CAST(t2.CustomProperties AS xml)
          .value('(/ArrayOfCustomProperty
                   /CustomProperty[(DeveloperId/text())[1]=sql:variable("@DeveloperId")
                                   and (Key/text())[1]=sql:variable("@Key")]
                   /Value
                   /text())[1]','nvarchar(max)');

The Path will look for a fitting <CustomProperty> and take its <Values>'s text()

Upvotes: 1

Ryan Wilson
Ryan Wilson

Reputation: 10765

You could try it like this:

SET LocationId = (SELECT TOP 1 x.value('/LocationId[1]') AS [LocationId]
FROM CAST(t2.CustomProperties AS xml).nodes('/ArrayOfCustomProperty/CustomProperty') AS a(x)
WHERE x.value('/LocationId[1]') IS NOT NULL)

Upvotes: 0

Related Questions