Need to query XML data inside a nvarchar column

Transact-SQL
1

Hi,

I have a table with XML data in a nvarchar column, named CheckoutAttributeXML and I need to extract the value for the attribute ID =2. Here is a sample of the data:

id   CreatedOnUtc	CheckoutAttributesXml
346601     2020-03-30 17:45:07.530    <Attributes><CheckoutAttribute ID="13"><CheckoutAttributeValue><Value>34</Value></CheckoutAttributeValue></CheckoutAttribute><CheckoutAttribute ID="14"><CheckoutAttributeValue><Value>Hannah Jewett</Value></CheckoutAttributeValue></CheckoutAttribute><CheckoutAttribute ID="15"><CheckoutAttributeValue><Value>Manager</Value></CheckoutAttributeValue></CheckoutAttribute><CheckoutAttribute ID="16"><CheckoutAttributeValue><Value>8772842351</Value></CheckoutAttributeValue></CheckoutAttribute><CheckoutAttribute ID="12"><CheckoutAttributeValue><Value>31</Value></CheckoutAttributeValue></CheckoutAttribute><CheckoutAttribute ID="3"><CheckoutAttributeValue><Value>text text text
    </Value></CheckoutAttributeValue></CheckoutAttribute><CheckoutAttribute ID="2"><CheckoutAttributeValue><Value>9196a907-f1d9-4ecf-875b-34d59ecd66cf</Value></CheckoutAttributeValue></CheckoutAttribute></Attributes>

I need to get the following results:

Id   ExtractedData
346601   9196a907-f1d9-4ecf-875b-34d59ecd66cf

I appreciate your help

2

Hello,

Please provide the xml data using the three code tick mark ` because we are not seeing the xml?

<note>

<to>Tove</to>

<from>Jani</from>

<heading>Reminder</heading>

<body>Don't forget me this weekend!</body>

</note>
3

Ok, figured out how to post my code.

4

I'm getting there but I need help on filtering my query to only get the CheckoutAttribute Id= 2, right now I'm getting all Attributes in the string

select 
a.Id,
x.y.value('CheckoutAttributeValue[1]', 'nvarchar(255)') as DownloadId
from 
(select id, Convert(XML, CheckoutAttributesXml) as XML, CreatedOnUtc from [order]) As A
    Cross Apply xml.nodes('//Attributes/CheckoutAttribute') As x(y)
where A.CreatedOnUtc > '2020-03-30 17:45:07.530'
5

hi

hope this links .. help :slight_smile:

6

Thanks but the links did not help. My query from above is pulling the data but since they are all CheckoutAttributes, it's pulling multiple rows. I need a filter so that I only get the value for the CheckoutAttribute of Id = 2
right now I'm getting the following results:

Id	DownloadId
346601	34
346601	Hannah Jewett
346601	Manager
346601	8772842351
346601	31
346601	Presented to
William Balthrop
1 Year
line break image
In Grateful Appreciation of
Outstanding Dedication and Service
logo
2020
346601	9196a907-f1d9-4ecf-875b-34d59ecd66cf

But I only want the last one, which has CheckoutAttribute id = 2

7 
;with src
as
(
  select cast(CheckoutAttributeXML  as xml) as xmlized
	From order
)
select X.Y.value('(*:Value)[1]', 'VARCHAR(30)') as Value,
       x.y.value('../@ID', 'int') AS 'ID'
  from src as T
 CROSS APPLY T.xmlized.nodes('//Attributes/CheckoutAttribute/CheckoutAttributeValue') as X(Y)
 where x.y.value('../@ID', 'int') = 2
2 Likes
8

Perfect!!!
This is exactly what I needed

9

Here is the finished query with the same values as the question -- this helps me so it may help others:

select 
a.Id,
x.y.value('../@ID', 'int') AS 'ID',
x.y.value('(*:Value)[1]', 'VARCHAR(30)') as DownloadId

from 
(select id, Convert(XML, CheckoutAttributesXml) as XML, CreatedOnUtc  from [order]) As A
	Cross Apply xml.nodes('//Attributes/CheckoutAttribute/CheckoutAttributeValue') As x(y)

where A.CreatedOnUtc > '2020-03-30 17:45:07.530' and a.Id = 346601 and x.y.value('../@ID', 'int') = 2

Yields:

Id	ID	DownloadId
346601	2	9196a907-f1d9-4ecf-875b-34d59e

... and now I can go a cleanup my 17 Gb download table for any orders older than 6 months.

I would not put the files in a table, but to each his own.

1 Like
10

it can also be done as

x.y.value('(Value)[1]', 'VARCHAR(30)') as Value