Xquery для извлечения атрибута внутреннего элемента

Question

У меня есть следующий xml в столбце xml с именем response в таблице с именем dbo.USR_YALE_IMODULES_EMAIL_WEBSERVICE_LOG. У меня проблемы с получением атрибутов Code и Description для элементов Error и Message. Может кто-нибудь объяснить, что я делаю не так? Я получаю 1727 строк ошибок, а их гораздо меньше. Я играл с

select response.query('declare namespace Error="http://imodules.com/WebServices/EmailCategoryQuery/Error";/Error:EmailCategoryUpdateResults/EmailCategoryUpdateMemberResult/Error[@Code[1]]') as result
FROM dbo.USR_YALE_IMODULES_EMAIL_WEBSERVICE_LOG 

это просто возвращает пробелы, хотя.

Заранее спасибо.

/ * образец xml

<EmailCategoryUpdateResults xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <EmailCategoryUpdateMemberResult xmlns="http://imodules.com/WebServices/EmailCategoryQuery/" Community_Id="2" Success="true" Constituent_Id="105933" Column="Email_Category_Omit_for_Travel_Program">
    <Message Code="2207" Description="The column 105933 already contains the specified update value." />
  </EmailCategoryUpdateMemberResult>
  <EmailCategoryUpdateMemberResult xmlns="http://imodules.com/WebServices/EmailCategoryQuery/" Community_Id="2" Success="false" Constituent_Id="105939" Column="Email_Category_Do_Not_Email">
    <Error Code="2500" Description="Could not find column Email_Category_Do_Not_Email." />
  </EmailCategoryUpdateMemberResult>
  <EmailCategoryUpdateMemberResult xmlns="http://imodules.com/WebServices/EmailCategoryQuery/" Community_Id="2" Success="true" Constituent_Id="105939" Column="Email_Category_Omit_AYA_Email">
    <Message Code= "2207" Description="The column 105939 already contains the specified update value." />
  </EmailCategoryUpdateMemberResult>
  <EmailCategoryUpdateMemberResult xmlns="http://imodules.com/WebServices/EmailCategoryQuery/" Community_Id="2" Success="true" Constituent_Id="105939" Column="Email_Category_Omit_for_Travel_Program">
    <Message Code="2207" Description="The column 105939 already contains the specified update value." />
  </EmailCategoryUpdateMemberResult>
  <EmailCategoryUpdateMemberResult xmlns="http://imodules.com/WebServices/EmailCategoryQuery/" Community_Id="2" Success="true" Constituent_Id="106016" Column="Email_Category_Omit_Travel_Email">
    <Message Code="2207" Description="The column 106016 already contains the specified update value." />
  </EmailCategoryUpdateMemberResult>
  <EmailCategoryUpdateMemberResult xmlns="http://imodules.com/WebServices/EmailCategoryQuery/" Community_Id="2" Success="true" Constituent_Id="106016" Column="Email_Category_Omit_Travel_Email">
  </EmailCategoryUpdateMemberResult>
</EmailCategoryUpdateResults>

*/

/*
create some temp tables
*/
If Object_Id('tempdb..#xml_transaction') IS NOT NULL BEGIN DROP TABLE #xml_transaction END; 
If Object_Id('tempdb..#xml_message') IS NOT NULL BEGIN DROP TABLE #xml_message END; 
If Object_Id('tempdb..#xml_error') IS NOT NULL BEGIN DROP TABLE #xml_error END; 

create table  #xml_transaction (
    TRANSACTIONID uniqueidentifier,
    LOOKUPID nvarchar(10),
    TRANSFERDATE nvarchar(25),
    CATEGORY nvarchar(155),
    LASTUPDATED nvarchar(25),
    COMMUNITYID nvarchar(1),
    OPTIN nvarchar(10),
    SUCCESS nvarchar(10)            
)

create table  #xml_message (
    MESSAGEID uniqueidentifier,
    LOOKUPID nvarchar(10),
    CATEGORY nvarchar(155),
    CODE nvarchar(25),
    DESCRIPTION nvarchar(100)
)           

create table  #xml_error (
    ERRORID uniqueidentifier,
    LOOKUPID nvarchar(10),
    CATEGORY nvarchar(155),
    CODE nvarchar(25),
    DESCRIPTION nvarchar(100)
)       

/* get the requests and responses for each transaction - some have messages or errors, and some do not. */  

INSERT INTO #xml_transaction
(
    TRANSACTIONID,
    TRANSFERDATE,
    LOOKUPID,
    CATEGORY,
    COMMUNITYID,
    LASTUPDATED,
    OPTIN,
    SUCCESS
)
SELECT
     id as trans_id
    ,transferdate   
    ,request.value(   
      'declare namespace Req="http://www.w3.org/2001/XMLSchema-instance";
        (/ArrayOfAllMemberOptOutResults/AllMemberOptOutResults/@Constituent_Id)[1]','varchar(20)') as lookup_id 
    ,request.value(
        'declare namespace Req="http://www.w3.org/2001/XMLSchema-instance";
         (/ArrayOfAllMemberOptOutResults/AllMemberOptOutResults/@Column)[1]','varchar(100)') as req_email_category  
    ,request.value(
        'declare namespace Req="http://www.w3.org/2001/XMLSchema-instance";
         (/ArrayOfAllMemberOptOutResults/AllMemberOptOutResults/@Community_Id)[1]','varchar(100)') as req_community_id              
    ,request.value(
        'declare namespace Req="http://www.w3.org/2001/XMLSchema-instance";
         (/ArrayOfAllMemberOptOutResults/AllMemberOptOutResults/@Last_Updated)[1]','varchar(100)') as req_last_updated
    ,request.value(
        'declare namespace Req="http://www.w3.org/2001/XMLSchema-instance";
         (/ArrayOfAllMemberOptOutResults/AllMemberOptOutResults/@OptIn)[1]','varchar(100)') as req_opt_in       
    ,response.value(
        'declare namespace Detail="http://imodules.com/WebServices/EmailCategoryQuery/";                
                                                            (//@Success)[1]','varchar(100)') as response_successOrFailure                       
    FROM dbo.USR_YALE_IMODULES_EMAIL_WEBSERVICE_LOG

/* get any messages */

INSERT INTO #xml_message
(
    MESSAGEID,
    LOOKUPID,
    CATEGORY,
    CODE,
    DESCRIPTION 
)
    select 
            id as message_id
           ,response.value(
          'declare namespace Detail="http://imodules.com/WebServices/EmailCategoryQuery/Message";               
                (//@Constituent_Id)[1]','varchar(100)') as message_lookupid 
            ,response.value(
          'declare namespace Detail="http://imodules.com/WebServices/EmailCategoryQuery/Message";               
                (//@Column)[1]','varchar(100)') as message_category   
            ,response.value(
            'declare namespace Detail="http://imodules.com/WebServices/EmailCategoryQuery/Message";             
                (//@Code)[1]','varchar(100)') as message_code
                ,response.value(
            'declare namespace Detail="http://imodules.com/WebServices/EmailCategoryQuery/Message";             
                (//@Description)[1]','varchar(100)') as message_description     
    FROM dbo.USR_YALE_IMODULES_EMAIL_WEBSERVICE_LOG     

/*  Get any errors */

INSERT INTO #xml_error
(
    ERRORID,
    LOOKUPID,
    CATEGORY,
    CODE,
    DESCRIPTION 
)                                                       
    select 
           id as error_id
          ,response.value(
          'declare namespace Detail="http://imodules.com/WebServices/EmailCategoryQuery/EmailCategoryUpdateMemberResult/Error";             
                (//@Constituent_Id)[1]','varchar(100)') as error_lookupid
          ,response.value(
          'declare namespace Detail="http://imodules.com/WebServices/EmailCategoryQuery/EmailCategoryUpdateMemberResult/Error";             
                (//@Column)[1]','varchar(100)') as error_category
            ,response.value(
            'declare namespace Detail="http://imodules.com/WebServices/EmailCategoryQuery/Error";               
                (//@Code)[1]','varchar(100)') as error_code
                ,response.value(
            'declare namespace Detail="http://imodules.com/WebServices/EmailCategoryQuery/Error";               
                (//@Description)[1]','varchar(100)') as error_description       
    FROM dbo.USR_YALE_IMODULES_EMAIL_WEBSERVICE_LOG;

Answer 1

Чтобы сделать запрос проще, вы можете использовать * символ для произвольного пространства имен. Например, этот запрос возвращает элементы Error:

    SELECT  
      response.query('/*:EmailCategoryUpdateResults/*:EmailCategoryUpdateMemberResult/*:Error')
    FROM dbo.USR_YALE_IMODULES_EMAIL_WEBSERVICE_LOG

Следующий запрос с явным пространством имен даст тот же результат:

    ;WITH XMLNAMESPACES('http://imodules.com/WebServices/EmailCategoryQuery/' AS q)
    SELECT
      response.query('/EmailCategoryUpdateResults/q:EmailCategoryUpdateMemberResult/q:Error')
    FROM dbo.USR_YALE_IMODULES_EMAIL_WEBSERVICE_LOG

Следующий запрос извлечет коды ошибок:

    SELECT response.value('(/*:EmailCategoryUpdateResults/*:EmailCategoryUpdateMemberResult/*:Error/@Code)[1]', 'varchar(100)')
    FROM dbo.USR_YALE_IMODULES_EMAIL_WEBSERVICE_LOG

Надеюсь, это поможет.