select value of specific attribute of xml document with multiple elements stored in SQL

Question

I have got the following XML document shown below stored in the NVARCHAR column xmlfile of my table exampletable. The order of the property-elements differs as well as the amount of elements.

<properties>
    <property name="LEAD" newValue="0" oldValue="" />
    <property name="CONTACT" newValue="0" oldValue="" />
    <property name="PROSPECT" newValue="0" oldValue="" />
</properties>

I'm able to select what I need as long as I specify the correct element with:

SELECT * FROM exampletable
WHERE CONVERT(xml,xmlfile).value('(/properties/property/@name)[1]', 'nvarchar(max)') = 'PROSPECT'

But how can I dynamically look for the needed attribute no matter on which position the element is?

Here is the SQL to create the example:

CREATE TABLE #exampletable
(     
    ID int IDENTITY(1,1) PRIMARY KEY,
    xmlfile NVARCHAR(max)
);
INSERT INTO #exampletable (xmlfile) 
VALUES ('<properties><property name="LEAD" newValue="0" oldValue="" /><property name="CONTACT" newValue="0" oldValue="" /><property name="PROSPECT" newValue="0" oldValue="" /></properties>')

Thanks a lot!

Alan Burstein · Accepted Answer · 2017-07-24 16:58:11Z

2

This is simple XPath and can be done without any CROSS APPLY statements.

Below is sample data with another record where the "@name = LEAD" element is not first.

-- Sample data with another record
USE tempdb
GO
IF OBJECT_ID('#exampletable') IS NOT NULL DROP TABLE #exampletable;
GO
CREATE TABLE #exampletable
(     
    ID int IDENTITY(1,1) PRIMARY KEY,
    xmlfile NVARCHAR(max)
);
INSERT INTO #exampletable (xmlfile)
VALUES 
('<properties>
<property name="LEAD" newValue="0" oldValue="" />
<property name="CONTACT" newValue="0" oldValue="" />
<property name="PROSPECT" newValue="0" oldValue="" />
</properties>'),
('<properties>
<property name="CONTACT" newValue="10" oldValue="5" />
<property name="LEAD" newValue="2" oldValue="1" />
<property name="PROSPECT" newValue="6" oldValue="" />
</properties>');
GO

-- Solution
SELECT 
  ID,
  oldValue = CAST(XMLfile AS xml).value('(/properties/property[@name="LEAD"]/@oldValue)[1]','varchar(10)'),
  newValue = CAST(XMLfile AS xml).value('(/properties/property[@name="LEAD"]/@newValue)[1]','varchar(10)')
FROM #exampletable;

Results:

ID          oldValue   newValue
----------- ---------- ----------
1                      0
2           1          2

answered Jul 24, 2017 at 16:58

Alan Burstein

8,0061 gold badge18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

btzs Over a year ago

thank you for response. it is a bit fast than mine! Following where condition works for me, to only show the entries with the attribute name with value LEAD: WHERE CAST(xmlfile as xml).exist('/properties/property[@name="LEAD"]') = 1

btzs · Accepted Answer · 2017-07-24 10:23:44Z

0

I got it myself with help from here https://stackoverflow.com/a/15136634/2893242

SELECT
    T.N.value('@name', 'varchar(50)') as name,
    T.N.value('@oldValue', 'varchar(50)') as oldValue,
    T.N.value('@newValue', 'varchar(50)') as newValue
FROM exampletable xt
cross apply (select cast(xmlfile as xml)) as S(F) 
cross apply S.F.nodes('/properties/property') T(N)
WHERE T.N.value('@name', 'varchar(50)') = 'LEAD' AND SA.id = @id

answered Jul 24, 2017 at 10:23

btzs

1,1083 gold badges14 silver badges17 bronze badges

Collectives™ on Stack Overflow

select value of specific attribute of xml document with multiple elements stored in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related