CODEWITHSUNDEEP
sql-serverxmlt-sql
chuckd
chuckd

Reputation: 14610

Pulling a value from Xml in a column

I have a table in Sql server that stores Xml data in one of its columns. The Xml column data looks like this:

<TestDef Weight="0" FailValue="2" ConceptID="-327">
  <ToleranceDef ObjectType="SomeName" TargetValue="0"TargetRange="2" />
</TestDef>

I need to write a query that fetches out all the conceptId's from each rows Xml column. Here it would be -327

I know I can cast the Xml column to a nvarchar(max) then use some reg exp to get the value but not sure how to use the regular expression

Upvotes: 3

Views: 373

Answers (1)

Josh Jay
Josh Jay

Reputation: 1250

Here's an example using a table variable. It will be the same concept with an actual table:

Declare @XmlTable table (
    Id Integer Identity,
    XmlValue XML
)

Insert Into @XmlTable (XmlValue) values ('<TestDef Weight="0" FailValue="2" ConceptID="-327"><ToleranceDef ObjectType="SomeName" TargetValue="0" TargetRange="2" /></TestDef>')
Insert Into @XmlTable (XmlValue) values ('<TestDef Weight="0" FailValue="2" ConceptID="-325"><ToleranceDef ObjectType="SomeName" TargetValue="0" TargetRange="2" /></TestDef>')


select
    Id,
    XmlValue,
    XmlValue.value('(/TestDef/@ConceptID)[1]', 'integer') as ConceptId
from
    @XmlTable

Upvotes: 5

Related Questions