Avatar of Paula DiTallo
Paula DiTallo
Flag for United States of America asked on

sql server xquery: namespace parsing

Techies--
I want to extrapolate the values within the tags with xquery but the namespace issue and nil="true" stuff has been a battle.  What I've posted as the code obviously doesn't work-- please review and correct.

DECLARE @applog_msg xml;
SET @applog_msg = '
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <Action s:mustUnderstand="1" xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none">http://www.metrocloud.com/ServiceContracts/LogService/Metrocloud.Framework.Logging.LogService</Action>
  </s:Header>
  <s:Body>
    <LogException xmlns="http://www.metrocloud.com/ServiceContracts/LogService">
      <exceptionLog xmlns:d4p1="http://www.metrocloud.com/2012/09/Logging" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <d4p1:ServiceDescription i:nil="true" />
        <d4p1:ServiceDomainName>TheServiceDomain</d4p1:ServiceDomainName>
        <d4p1:Type>AnyType</d4p1:Type>
      </exceptionLog>
    </LogException>
  </s:Body>
</s:Envelope>';


   SELECT
    T.c.value('d4p1:ServiceDescription[1] i:nil="true" />') as ServiceDescription,
    T.c.value('d4p1:ServiceDomainName[1]', 'varchar(100)') as ServiceDomainName,
    T.c.value('d4p1:Type[1]', 'varchar(10)') as [Type]
     FROM @applog_msg.nodes('LogException/exceptionLog') as T(c);
     
  

Open in new window

Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Paula DiTallo

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Saurabh Bhadauria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Paula DiTallo

ASKER
Brilliantly done! Thank you!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23