CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
Kirill Rodeonov
Kirill Rodeonov

Reputation: 145

How to parse XML data in SQL server table

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

Upvotes: 6

Views: 61771

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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...

Upvotes: 3

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

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)

Upvotes: 6

vadzim dvorak
vadzim dvorak

Reputation: 959

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)

Upvotes: 4

Related Questions