sql server - Parsing XML from returned field in SQL -
i have field called xml
contains data need extract. contents of xml
field this:
<p> <k>attribute name</k> <v>attribute value</v> </p> <p> <k>attribute name 2</k> <v>attribute value 2</v> </p> <p> <k>attribute name 3</k> <v>attribute value 3</v> </p> <p>... , on ...</p>
is there way can write query extract attribute value 2
in sql? normal practice vendors?
edit:
i'm able query off xml field using m.[xml].value('/p[2]','varchar(max)'
cannot figure out way grab direct neighbor of node key looking for. example, want grab <v>attribute value 2</v>
search off <k>attribute name 2</k>
you did not accept answer yet, might be, existing answers did not help...
if correctly, want read value (=<v>
) given key (=<k>
). try this:
declare @xml xml = n'<p> <k>attribute name</k> <v>attribute value</v> </p> <p> <k>attribute name 2</k> <v>attribute value 2</v> </p> <p> <k>attribute name 3</k> <v>attribute value 3</v> </p>'; declare @search nvarchar(100)=n'attribute name 2'; select @xml.value(n'(/p[k/text()=sql:variable("@search")]/v/text())[1]',n'nvarchar(max)');
the key-name looking set variable @search
.
the xquery
looking <p>
, there <k>
has got text looking for.
then <v>
below <p>
read , returned.
Comments
Post a Comment