How to parse XML data in SQL server table

user_9256080 0
Viewed 32509

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
xml tsql sql-server sql
3 Answers

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)
user_51061540

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)
user_49047260

It is a very bad idea to store XML data in a string column.

Even worse, you are storing a string, which is claiming I'm encoded as utf-8!, but it is stored in a column of NVARCHAR, which is UCS-2 (almost the same as utf-16).

That means, that you have to repair this and cast the string to XML before you can use it. You'll have to do these expensive operations whenever you grab into it. If you can change this, you should store XML in a column natively typed.

Nevertheless this can be done. Try this.

Create table #temp
(
ID varchar(50) not null,
[Action] nvarchar(max) null
)

INSERT INTO #temp(ID,[Action]) VALUES
('test 1',
'<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order>   
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
    </oo_master> 
    </oo_outbound_order>');

SELECT t.ID
      ,ActionXml.value('(/oo_outbound_order/oo_master/Code/text())[1]','nvarchar(max)') AS CODE
      ,ActionXml.value('(/oo_outbound_order/oo_master/Name/text())[1]','nvarchar(max)') AS [Name]
FROM #temp t
CROSS APPLY(SELECT CAST(REPLACE(t.[Action],'encoding="UTF-8"','encoding="UTF-16"') AS XML) ActionXml) A;

drop table #temp;

Hint: If there are no repeating elements, there is no need for .nodes() as other answers suggest...

user_50892040
Related