Solved

sql server xquery: namespace parsing

Posted on 2012-12-20
2
619 Views
Last Modified: 2012-12-21
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

0
Comment
Question by:ditallop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 12

Accepted Solution

by:
Saurabh Bhadauria earned 500 total points
ID: 38712188
Try like this....

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>';

;WITH XMLNAMESPACES(
    'http://schemas.xmlsoap.org/soap/envelope/' AS s,
      'http://www.metrocloud.com/2012/09/Logging' as d4p1,
      'http://www.w3.org/2001/XMLSchema-instance' as i,
    DEFAULT 'http://www.metrocloud.com/ServiceContracts/LogService' 
    
)


   SELECT
    T.c.value('d4p1:ServiceDescription[1]/@i:nil','varchar(100)') 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('s:Envelope/s:Body/LogException/exceptionLog') as T(c);
     
  

Open in new window

0
 

Author Comment

by:ditallop
ID: 38713036
Brilliantly done! Thank you!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question