I have a column that contains an XML like this:
<metadata>
<meta id="TypeX" valuetype="xs:string">
<values>
<value>3</value>
</values>
</meta>
<meta id="TypeY" valuetype="xs:string">
<values>
<value>5</value>
</values>
</meta>
</metadata>
and I need to query it by meta Tag Attribute 'id'.
I need to have foreach 'meta' tag his id and value.
Here you have something that should help:
declare @xml xml
set @xml = '<metadata>
<meta id="TypeX" valuetype="xs:string">
<values>
<value>3</value>
</values>
</meta>
<meta id="TypeY" valuetype="xs:string">
<values>
<value>5</value>
</values>
</meta>
</metadata>'
select C.value('(./@id)', 'varchar(5)') AS [Meta ID]
, C.value('(./values/value)[1]', 'tinyint') AS [Value]
from @xml.nodes('/metadata/meta') AS T(C)
Moreover, it could be useful for you to get familiar with XQueries :)
You can change data type in select statement from varchar(5) to some bigger varchar or from tinyint to some "bigger" type, if you need to.
<value> nodes below <values> (it looks like this...), you will read the first one only. Secondly, especially with bigger XMLs, it is not performant to read the whole lot into a derived table, just to do the filtering externally.If your XML is big, it will lead to a bad performance, if you first shred the whole thing, just to filter it afterwards. It is better to include your filter as predicate into your XQuery:
DECLARE @xml XML=
N'<metadata>
<meta id="TypeX" valuetype="xs:string">
<values>
<value>3</value>
</values>
</meta>
<meta id="TypeY" valuetype="xs:string">
<values>
<value>5</value>
<value>6</value>
</values>
</meta>
</metadata>';
--This is the variable holding the id's value
DECLARE @id VARCHAR(10)='TypeX';
--This SELECT reads the first <value> within <values> as one-liner:
SELECT @xml.value(N'(/metadata/meta[@id=sql:variable("@id")]/values/value/text())[1]','int');
--Change the filter-variable
SET @id='TypeY'
--This query will use .nodes() to get all <value> nodes within <values> (if there are more of them)
SELECT v.value('text()[1]','int')
FROM @xml.nodes(N'/metadata/meta[@id=sql:variable("@id")]/values/value') AS A(v);
One more hint: If you are sure, that there is only one entry per @id, you might extend the predicate to [@id=sql:variable("@id")][1]. This will prevent the engine to continue searching for nodes with this id.