text() for xml Data Type Methods
-
I have a t-sql:
exec @ret = uspAAA '<?xml version="1.0"? > < SerialNumber><S Descr="SerialNumber">2283 </S> </SerialNumber>'
inside uspAAA it was doing this for MSSQL 2000 :
insert @SSNField (N, V_xml)
select N, D from openxml(@iDoc, '/SerialNumber/S') with (N varchar(200) '@Descr', D varchar(800) 'text()')
to get the "2283"
But what if I want to use XML datatype,
insert @SSNField (N, V_xml)--select N, D from openxml(@iDoc, '/SerialNumber/S') with (N varchar(200) '@Descr', D varchar(800) 'text()')
select
tab.col.value('@Descr', 'varchar(100)'),
??<--what?
from
@xml.nodes('/SerialNumber/S') tab(col)
-
the one I could find is:
insert @SSNField (N, V_xml)--select N, D from openxml(@iDoc, '/SerialNumber/S') with (N varchar(200) '@Descr', D varchar(800) 'text()')
select
tab.col.value('@Descr', 'varchar(100)'),
convert(varchar, @xml.query('/SerialNumber/S/text()'))
from
@xml.nodes('/SerialNumber/S') tab(col)
but I would prefer to refer to relative path, instead of specifying absolute path twice.
-
selecttab.col.value('@Descr', 'varchar(100)'),
tab.col.value('.', 'varchar(100)')
from @xml.nodes('/SerialNumber/S') tab(col)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
thanks. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply