EXTRACT RESULTS FROM XML node with namespace using SQL

Question

I Have the below XML and want to extract the values for the following Nodes 1. result 2. documentNumber 3. costElementCode

<commitmentsResponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <result xmlns="http://response.cim.its.test.edu.au/">SUCCESS</result>
  <value xmlns="http://finance.response.cim.its.test.edu.au/">
    <documentNumber xmlns="http://finance.cim.its.test.edu.au/">12345</documentNumber>
      <lineNumber>2</lineNumber>
      <costElementCode>costElementCode</costElementCode>
      <internalOrderNumber>1000002</internalOrderNumber>
      <costCentreCode>9999</costCentreCode>
      <wbsCode>3000</wbsCode>
      <lineDescription>2 packets of pencils</lineDescription>
      <accountNumber>100000</accountNumber>
      <itemAmount>105.5</itemAmount>
      <fundsDueDate>2015-06-15</fundsDueDate>
    </commitmentLine>
    <commitmentLine xmlns="http://finance.cim.its.test.edu.au/">
      <lineNumber>2</lineNumber>
      <costElementCode>costElementCode</costElementCode>
      <internalOrderNumber>1000002</internalOrderNumber>
      <costCentreCode>9999</costCentreCode>
      <wbsCode>3000</wbsCode>
      <lineDescription>2 packets of pencils</lineDescription>
      <accountNumber>100000</accountNumber>
      <itemAmount>105.5</itemAmount>
      <fundsDueDate>2015-06-15</fundsDueDate>
    </commitmentLine>
  </value>
  </commitmentsResponse>

Answer 1

Without using Namespaces:

DECLARE @myXML xml =  
N'<commitmentsResponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <result>SUCCESS</result>
  <value>
    <documentNumber>12345</documentNumber>
    <commitmentLine>
      <lineNumber>2</lineNumber>
      <costElementCode>costElementCode</costElementCode>
      <internalOrderNumber>1000002</internalOrderNumber>
      <costCentreCode>9999</costCentreCode>
      <wbsCode>3000</wbsCode>
      <lineDescription>2 packets of pencils</lineDescription>
      <accountNumber>100000</accountNumber>
      <itemAmount>105.5</itemAmount>
      <fundsDueDate>2015-06-15</fundsDueDate>
    </commitmentLine>
    <commitmentLine xmlns="http://finance.cim.its.test.edu.au/">
      <lineNumber>2</lineNumber>
      <costElementCode>costElementCode</costElementCode>
      <internalOrderNumber>1000002</internalOrderNumber>
      <costCentreCode>9999</costCentreCode>
      <wbsCode>3000</wbsCode>
      <lineDescription>2 packets of pencils</lineDescription>
      <accountNumber>100000</accountNumber>
      <itemAmount>105.5</itemAmount>
      <fundsDueDate>2015-06-15</fundsDueDate>
    </commitmentLine>
  </value>
  </commitmentsResponse>'

  DECLARE @DocumentNumber INT  
  SELECT @DocumentNumber = [Table].[Column].value('documentNumber[1]', 'INT') 
                                 FROM @myXML.nodes('/commitmentsResponse/value') AS [Table]([Column])

  DECLARE @Result VARCHAR(256) 
  SELECT @Result = [Table].[Column].value('result[1]', 'varchar(256)') 
                                 FROM @myXML.nodes('/commitmentsResponse') AS [Table]([Column])

  DECLARE @CostElementCode VARCHAR(256)
  SELECT @CostElementCode = [Table].[Column].value('costElementCode[1]', 'varchar(256)') 
                                 FROM @myXML.nodes('/commitmentsResponse/value/commitmentLine') AS [Table]([Column])

  SELECT @Result
  SELECT @DocumentNumber
  SELECT @CostElementCode

With using namespaces:

DECLARE @myXML xml =  
N'<commitmentsResponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <result xmlns="http://response.cim.its.test.edu.au/">SUCCESS</result>
  <value>
    <documentNumber xmlns="http://finance.cim.its.test.edu.au/">12345</documentNumber>
    <commitmentLine>
      <lineNumber>2</lineNumber>
      <costElementCode>costElementCode</costElementCode>
      <internalOrderNumber>1000002</internalOrderNumber>
      <costCentreCode>9999</costCentreCode>
      <wbsCode>3000</wbsCode>
      <lineDescription>2 packets of pencils</lineDescription>
      <accountNumber>100000</accountNumber>
      <itemAmount>105.5</itemAmount>
      <fundsDueDate>2015-06-15</fundsDueDate>
    </commitmentLine>
    <commitmentLine xmlns="http://finance.cim.its.test.edu.au/">
      <lineNumber>2</lineNumber>
      <costElementCode>costElementCode</costElementCode>
      <internalOrderNumber>1000002</internalOrderNumber>
      <costCentreCode>9999</costCentreCode>
      <wbsCode>3000</wbsCode>
      <lineDescription>2 packets of pencils</lineDescription>
      <accountNumber>100000</accountNumber>
      <itemAmount>105.5</itemAmount>
      <fundsDueDate>2015-06-15</fundsDueDate>
    </commitmentLine>
  </value>
  </commitmentsResponse>'

  DECLARE @DocumentNumber INT  
;WITH XMLNAMESPACES (N'http://finance.cim.its.test.edu.au/' as DYN)
 SELECT @DocumentNumber = c.value('(DYN:documentNumber)[1]', 'INT')
 FROM    @myXML.nodes('/commitmentsResponse/value') t(c)


 DECLARE @Result VARCHAR(256) 
;WITH XMLNAMESPACES (N'http://response.cim.its.test.edu.au/' as DYN)
 SELECT @Result = c.value('(DYN:result)[1]', 'VARCHAR(256)')
 FROM    @myXML.nodes('/commitmentsResponse') t(c)


  DECLARE @CostElementCode VARCHAR(256)
  SELECT @CostElementCode = c.value('(costElementCode)[1]', 'VARCHAR(256)')
  FROM    @myXML.nodes('/commitmentsResponse/value/commitmentLine') t(c)


  SELECT @Result
  SELECT @DocumentNumber
  SELECT @CostElementCode