How to parse XML data in SQL server table

Question

I have SQL table:

Create table
(
ID varchar(50) not null,
Action nvarchar(max) null
)

Action column contains XML data. Format:

<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order>   
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
    </oo_master> 
    </oo_outbound_order>

How to parse this column? Result should be like this:

CODE    NAME
123     Branan

Answer 1

Try the follwing meathod :

DECLARE @XMLData XML = '
<oo_outbound_order>   
    <oo_master>
        <Code>123</Code>
        <Name>Branan</Name> 
    </oo_master> 
</oo_outbound_order>'

SELECT
    [Code] = Node.Data.value('Code', 'INT'),
    [Name] = Node.Data.value('Name', 'NVARCHAR(20)')
    FROM @XMLData.nodes('/oo_outbound_order/oo_master') Node(Data)

Answer 2

There are many tutorial articles about xml parsing with TSQL. For example, http://www.sqlserver.info/syntax/parse-xml-with-sql-server/

DECLARE @xml xml
SET @xml = 
'<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order>   
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
    </oo_master> 
    </oo_outbound_order>'

SELECT 
    n.value('(./Code/text())[1]','int') as CODE
 , n.value('(./Name/text())[1]','Varchar(50)') as NAME
FROM @xml.nodes('/oo_outbound_order/oo_master') as a(n)