Reading xml data in SQL

Question

I am reading data from xml in Sql.

Here is my xml :

Declare @MainXml XML = 
'<?xml version="1.0" encoding="utf-8"?>
  <result>
   <details>
     <admin>
        <code>555</code>
     </admin>
    <claimhistory>
        <claim id="1" number="100">
            <account>Closed</account>           
        </claim>        
        <claim id="2" number="200">         
            <account>Closed</account>                       
         </claim>        
     </claimhistory>
 </details>
</result>'

Reading data like this:

select 
    C.X.value('(admin/code)[1]', 'varchar(max)') as Code,
    A.X.value('@id', 'varchar(max)') as Id,
    A.X.value('@number', 'varchar(max)') as No,            
    A.X.value('(account)[1]', 'varchar(max)') as Status
from 
    @MainXml.nodes('result/details') as C(X)
cross apply 
    C.X.nodes('claimhistory/claim') as A(X)

This is returning:

Code  Id  No  Status 
---------------------
555   1   100  Closed
555   2   200  Closed

Stored procedure contains above code.

Here datatable variable is used as an input for Stored Procedure. It contains id and name.

 Declare @dtValue As [dbo].[DataTableDetails]
 Insert Into @dtValue(Requested_Id, Name) Values(1, 'Tim');
 Insert Into @dtValue(Requested_Id, Name) Values(2, 'Joe');

I want to add these names to select query based on matching Id of an xml to input.

Expected output -

Code  Id  No   Status   Name
----------------------------
555   1   100  Closed   Tim
555   2   200  Closed   Joe

Currently - after inserting the selected records from xml, I am using update query But table contains over a million records so it is effecting performance now.

Please suggest me.

Edited:

Tried with Join - [added below line in select query]

Select 
    C.X.value('(admin/code)[1]', 'varchar(max)') as Code,
    A.X.value('@id', 'varchar(max)') as Id,
    A.X.value('@number', 'varchar(max)') as No,            
    A.X.value('(account)[1]', 'varchar(max)') as Status,
    CA.Name
from 
    @MainXml.nodes('result/details') as C(X)
cross apply 
    C.X.nodes('claimhistory/claim') as A(X)
join 
    @dtValue CA on CA.Requested_Id = A.X.value('@id', 'varchar(max)')

Edited by adding joins. Is there any better approach considering the performance. — user1893874
– user1893874, Commented Feb 1, 2016 at 15:56
I've removed the MySQL tag because your question didn't seem to be related to such DBMS. If relationship exists, please edit the question and explain it. — Álvaro González
– Álvaro González, Commented Feb 1, 2016 at 15:58
user1893874, this is a very good question! You stated sample data, your own attempts, expected output, details without being to long... voted it up and voted the answer up. And this is, what you should do with the answer to. Accpeting and voting are two different steps, and I'm sure @DanField will be happy about some vote points :-) — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 1, 2016 at 23:23

Dan Field · Accepted Answer · 2016-02-01 16:40:50Z

I'd recommend refactoring the way you're selecting from the XML like so:

select 
    C.X.value('(../../admin/code)[1]', 'varchar(max)') as Code,
    C.X.value('@id', 'varchar(max)') as Id,
    C.X.value('@number', 'varchar(max)') as No,            
    C.X.value('(account)[1]', 'varchar(max)') as Status,
    dt.Name
from 
    @MainXml.nodes('result/details/claimhistory/claim') as C(X)
INNER JOIN @dtValue dt
ON dt.Requested_Id = C.X.value('(@id)[1]', 'int')

You don't actually want to CROSS APPLY the child nodes, you want them to be the primary part you're selecting from (i.e. one row per claim element) - it's then easy enough to select based on the grandparent node to get the Code value, and then you can properly INNER JOIN your table variable.

Full sample:

Declare @MainXml XML = 
'<?xml version="1.0" encoding="utf-8"?>
  <result>
   <details>
     <admin>
        <code>555</code>
     </admin>
    <claimhistory>
        <claim id="1" number="100">
            <account>Closed</account>           
        </claim>        
        <claim id="2" number="200">         
            <account>Closed</account>                       
         </claim>        
     </claimhistory>
 </details>
</result>'

DECLARE @dtValue TABLE (Requested_Id int, Name varchar(10))

 Insert Into @dtValue(Requested_Id, Name) Values(1, 'Tim'), (2, 'Joe');

select 
    C.X.value('(../../admin/code)[1]', 'varchar(max)') as Code,
    C.X.value('@id', 'varchar(max)') as Id,
    C.X.value('@number', 'varchar(max)') as No,            
    C.X.value('(account)[1]', 'varchar(max)') as Status,
    dt.Name
from 
    @MainXml.nodes('result/details/claimhistory/claim') as C(X)
INNER JOIN @dtValue dt
ON dt.Requested_Id = C.X.value('(@id)[1]', 'int')

Collectives™ on Stack Overflow

Reading xml data in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related