asked
How to query XML data from SQL 2008 db with SOAP-ENV
I'm trying to parse/query XML data from a SQL 2008 db column but only get null values in the XML fields. There can be multiple RIC & RID tags. I have tried several different queries even with CROSS APPLY but get syntax errors. Any help would be appreciated. Thank you. My query is: SELECT FName, LName, DOB, State, RespRslt, Resprecvd.value('(TXL/TLR/TRlt/RI/RIC)[1]','nvarchar(max)') AS ResultCode, Resprecvd.value ('(TXL/TLR/TRlt/RI/RID)[1]','nvarchar(max)') AS ResultDesc FROM ( SELECT FName, LName, DOB, Res_State as State, Resp_Reslt as RespRslt, XML_Received AS Resprecvd FROM XXX_Request ) AS P WHERE RespRslt = 'Failure' ResultCode and ResultDesc return 'Null' values. XML_Received column contains: 99999-xxxx-9999 XXX Update 2013-07-09 22:30:48Z ORIGINAL Check Service Failure Required Element Invalid Invalid Character
Your query is almost there, except it misses the reference to the default namespace. This is why you get NULL in the values. Try this and it should work:
;with xmlnamespaces (default 'http://XXXX.org/XXXXXXX/XXXX/2')
SELECT
FName, LName, DOB, State, RespRslt,
Resprecvd.value('(TXL/TLR/TRlt/RI/RIC)[1]','nvarchar(max)') AS ResultCode,
Resprecvd.value ('(TXL/TLR/TRlt/RI/RID)[1]','nvarchar(max)') AS ResultDesc
FROM
(
SELECT
FName,
LName,
DOB,
Res_State as State,
Resp_Reslt as RespRslt,
XML_Received AS Resprecvd
FROM XXX_Request
) AS P
WHERE RespRslt = 'Failure'
Thank you for your help but I still get null values. The 'default' keyword has an error 'Incorrect syntax near 'default', expecting ID or Quoted_ID'.
@taylorann62 Are you sure you have the namespace enclosed in the single quotes and the semicolon before the word WITH? I created the sample table based on the info in your question, and the statement worked as expected. Actually, I would never post an answer to the question on this site if the query I post does not parse. If you copied the first line from my script to the top of yours and it still did not work then the only other possibility is that your database is somehow at compat 80. This is very rarely possible but it is possible nevertheless. For example, if you upgrade your instance from SQL Server 2000 to 2005 and then from 2005 to 2008 or 2008 R2 then the compat level remains at 80. This is by design, because at Microsoft they don't want us to break any existing code until we are sure that it is safe to set the compat level to appropriate level (100 for SQL Server 2008). Please check:
select compatibility_level
from sys.databases
where name ='your_db_name'
If the above returns 80 then set it to a higher level if you can afford it:
alter database your_db_name set compatibility_level = 100;
@taylorann62 Another possibility is the xml you posted in the question is not all that you actually have in the column. I mean **XML\_Received** column contains some other nodes which are parents of what is posted. If this is the case, please post the entire contents of the column from one of the existing records because it is important to figure out the correct xpath when querying xml data.
15 People are following this question.
