troubleshooting Question

sql server xquery: namespace parsing

Avatar of Paula DiTallo
Paula DiTalloFlag for United States of America asked on
Microsoft SQL Server 2008
2 Comments1 Solution825 ViewsLast Modified:
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);
     
  
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros