CODEWITHSUNDEEP
sqlxmlsql-server-2008t-sql
Eric
Eric

Reputation: 8078

searching for specific entity name in sql xml table columns

Is it possible to search for entity names in an sql column?

For example If i have a column that looks like 

<ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<EntityPropertyOfString>
<Name>User Label 1</Name>
<Value>TX</Value>
 </EntityPropertyOfString>
 <EntityPropertyOfString>
<Name>PD Rate</Name>
<Value>0</Value>
</EntityPropertyOfString>
<EntityPropertyOfString>
<Name>Bonus Day Rate</Name>
<Value>300</Value>
</EntityPropertyOfString>
</ArrayOfEntityPropertyOfString>

How can I select all the records that have a 'Bonus Day Rate' to get its value??

Thank you in advance

Upvotes: 1

Views: 247

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

declare @T table (ID int identity primary key, XMLCol xml)

declare @xml xml =
'<ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<EntityPropertyOfString>
<Name>User Label 1</Name>
<Value>TX</Value>
 </EntityPropertyOfString>
 <EntityPropertyOfString>
<Name>PD Rate</Name>
<Value>0</Value>
</EntityPropertyOfString>
<EntityPropertyOfString>
<Name>Bonus Day Rate</Name>
<Value>300</Value>
</EntityPropertyOfString>
</ArrayOfEntityPropertyOfString>'

insert into @T values (@xml)
insert into @T values (@xml)

select T1.ID,
       T2.N.value('Value[1]', 'int') as Value
from @T as T1
  cross apply T1.XMLCol.nodes('/ArrayOfEntityPropertyOfString/EntityPropertyOfString[Name = "Bonus Day Rate"]') as T2(N)

Result:

ID          Value
----------- -----------
1           300
2           300

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Here's one way:

SELECT @x.query('(//EntityPropertyOfString[./Name="Bonus Day Rate"]/Value)').value('(.)[1]', 'int')

Basically, you find all the nodes named EntityPropertyOfString, and look for the specific one that has a child sub-node of Name with a value of "Bonus Day Rate. That position is indicated by the square brackets; once you have that position, you query the Value child-node for the first position, and cast it to an int.

Upvotes: 1

Related Questions