CODEWITHSUNDEEP
sql-serverxmlt-sql
Mardok
Mardok

Reputation: 664

Select data from complicated xml in sql server

I have this XML:

<Articles> 
    <Article> 
        <ArticleNo>31213496</Article_No> 
        <ArticleDes>PRESSNING</ArticleDes> 
        <Notes></Notes> 
        <EstimatedCycleTime>2.05714285714286</EstimatedCycleTime> 
        <Departments> 
            <Department isLinked="true" name="Finnveden Olofström" /> 
            <Department isLinked="false" name="3333333333" /> 
            <Department isLinked="true" name="company_hide" /> 
          <Department isLinked="false" name="Department1" /> 
      </Departments> 
    </Article> 
</Articles>

I use this SQL to select Articles data:

declare  
        @articleNo nvarchar(25), 
        @articleDes nvarchar(255), 
        @notes nvarchar(max), 
        @cycleTime float  
declare cls cursor scroll for 
        select a.value('Article_No[1]','nvarchar(25)'), a.value('ArticleDes[1]','nvarchar(255)'),a.value('Notes[1]','nvarchar(max)'),a.value('EstimatedCycleTime[1]','float') 
        from @xml.nodes('Articles/Article') as a(a) 
open cls 
fetch next from cls into @articleNo, @articleDes, @notes, @cycleTime 
while @@FETCH_STATUS = 0 
begin 
        select @articleNo, @articleDes, @notes, @cycleTime 
        fetch next from cls into @articleNo, @articleDes, @notes, @cycleTime 
end 
close cls 
deallocate cls

How do I select departments names which have isLinked="true" for each Article?

Upvotes: 2

Views: 49

Answers (1)

marc_s
marc_s

Reputation: 754538

Absolutely no need for a messy cursor here! Just use the native XQuery support in SQL Server:

SELECT
    a.value('Article_No[1]','nvarchar(25)'), 
    a.value('ArticleDes[1]','nvarchar(255)'),
    a.value('Notes[1]','nvarchar(max)'),
    a.value('EstimatedCycleTime[1]', 'decimal(28,14)') ,
    DepartmentName = d.value('@name', 'nvarchar(50)')
FROM
    @xml.nodes('Articles/Article') as a(a) 
CROSS APPLY
    a.nodes('Departments/Department') AS d(d)
WHERE
    d.value('@isLinked', 'varchar(10)') = 'true'

This will give an output of:

enter image description here

Since you have multiple departments for each <Article> node, you need to use the CROSS APPLY operator and define yet another .nodes() list of XML subelements to get at all the departments defined.

Upvotes: 3

Related Questions