May 24, 2012 at 2:38 pm
Hello all,
I'm having some trouble selecting out some values from an XML column. I believe my issues are caused in
part by the namespace being incorporated into each tag, but everything I try either results in a syntax error or a blank string.
Here is the T-SQL I'm trying to use and a sample of the XML is attached.
SELECT
xml_column.query('data(/VehicleValue_Struc/VehicleYear)') AS vehicle_year
FROM
vehicles
WHERE
id = 10
Thanks!
May 24, 2012 at 3:10 pm
;WITH xmlnamespaces (N'http://webservice.nada.com/' as ns)
SELECT
xml_column.value(N'(//VehicleValue_Struc/ns:VehicleYear)[1]',N'nvarchar(100)') AS vehicle_year
FROM
vehicles
WHERE
id = 10
OR:
;WITH xmlnamespaces (N'http://webservice.nada.com/' as ns)
SELECT
xml_column.query(N'data(//VehicleValue_Struc/ns:VehicleYear)[1]') AS vehicle_year
FROM
vehicles
WHERE
id = 10
_________________________________
seth delconte
http://sqlkeys.com
May 24, 2012 at 3:22 pm
You, sir, are my hero. So you need to declare xmlnamespaces whenever the namespace is included in the XML tag?
Thanks again!
May 24, 2012 at 3:27 pm
Yes. You can also do it inline like this:
SELECT
xml_column.query('declare namespace ns="http://webservice.nada.com/"; data(//VehicleValue_Struc/ns:VehicleYear)[1]') AS vehicle_year
FROM
vehicles
WHERE
id = 10
And you are welcome 😀
_________________________________
seth delconte
http://sqlkeys.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply