title: Replacing values in the XML nodes to reference nodes correctly using XQUERY and filter required values
body: <p>I've have a table which contains XML data with several nodes in one of the columns. Something similar to the below.</p>
  <pre><code>&lt;SendWorkOrders xmlns:xsd=&quot;http://www.w3.org/2001/XMLSchema&quot;
                  xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;
                  creationDateTime=&quot;2021-06-05T10:16:09.713+12:00&quot;
                  requestCorrelationId=&quot;63cbec24c87a1bfa88999ef373e&quot;
                  xmlns=&quot;http://mycompany.co.nz/customerservices/servicepartner/workorder&quot;&gt;
      &lt;WORKORDERSET&gt;
          &lt;WORKORDER action=&quot;Replace&quot;&gt;
              &lt;WONUM&gt;123456&lt;/WONUM&gt;
              &lt;DESCRIPTION&gt;My work order&lt;/DESCRIPTION&gt;
          &lt;/WORKORDER&gt;
      &lt;/WORKORDERSET&gt;
  &lt;/SendWorkOrders&gt;
  </code></pre>
  <p>I want to change the format to something like below in order to use XQUERY and filter required nodes.</p>
  <pre><code>&lt;SendWorkOrders&gt;
  &lt;WORKORDERSET&gt;
  &lt;WORKORDER&gt;
  &lt;WONUM&gt;123456&lt;/WONUM&gt;
  &lt;DESCRIPTION&gt;My work order&lt;/DESCRIPTION&gt;
  &lt;/WORKORDER&gt;
  &lt;/WORKORDERSET&gt;
  &lt;/SendWorkOrders&gt;
  </code></pre>
  <p>Here's more details.</p>
  <p>I have a table called 'WorkOrderLogs' which has 4 columns with EventId,Source,Timestamp,SendWorkOrders. SendWorkOrders column stores the raw XML from customer as mentioned in the first code snippet. I'm trying to reference specific nodes in the XML value using the query below.</p>
  <pre><code>SELECT e.EventId,e.Timestamp,e.SendWorkOrder,e.Source,
  X.Y.value('(WONUM)[1]','VARCHAR(20)') as WONUMBER,
  X.Y.value('(DESCRIPTION)[1]','VARCHAR(256)') as DESCRIPTION
  From WorkOrderLogs e
  OUTER APPLY e.SendWorkOrders.nodes('SENDWORKORDERS/WORKORDERSET/WORKORDER/WONUM') as X(Y)
  </code></pre>
  <p>It returns NULL values as my root node/namespace contains additional information of the schemas and other details. So, I'm thinking if i am able to format the value to something simple then i can achieve what i want here.</p>
  <p>I managed write a query to pick the nodes, but failed to reference them correctly. Root nodes contain schema and additional information unique for each message. I couldn't simplify it to target the required nodes. I'm fairly new to T-SQL, so any help/pointers are highly appreciated.</p>
  <p>Thanks in advance.</p>
  <p>Update:</p>
  <p>Thank you so much Yitzhak Khabinsky. It worked like a charm for one use case. But, I got another problem now. The namespace exists in child nodes.</p>
  <p>Sample value is something like this.</p>
  <pre><code>&lt;OrderNotification xmlns:xsd=&quot;http://www.w3.org/2001/XMLSchema&quot; xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;&gt;
          &lt;POSKI xmlns=&quot;http://MyCompany.co.nz&quot;&gt;12345&lt;/POSKI&gt;
          &lt;BUKRS xmlns=&quot;http://MyCompany.co.nz&quot;&gt;ABCD&lt;/BUKRS&gt;
          &lt;PRCTR xmlns=&quot;http://MyCompany.co.nz&quot;&gt;1324&lt;/PRCTR&gt;
      &lt;/OrderNotification&gt;
  </code></pre>
  <p>Query I'm trying is</p>
  <pre><code>DECLARE @OrderLogs TABLE ( OrderNotification XML);
  INSERT INTO @ProjectLogs (ProjectNotification)
  (SELECT Info
        
    FROM [MainLogging].[dbo].[JobLogs]
  
    where EventId = 'XXXXXXXX'
    and Message ='OrderNotification');
  
    WITH XMLNAMESPACES (DEFAULT 'http://mycompany.co.nz')
    SELECT c.value('(POSKI/text())[1]','VARCHAR(20)') AS ORDER
      , c.value('(BUKRS/text())[1]','VARCHAR(256)') AS DESCRIPTION
      FROM @ProjectLogs
      CROSS APPLY OrderNotification.nodes('/OrderNotification') AS t(c);
  </code></pre>