Select data from complicated xml in sql server

Question

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?

If you post code, XML or data samples, PLEASE highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! That way, you can avoid all those messy  , <br/> etc. tags, too!! — marc_s
– marc_s, Commented Aug 15, 2014 at 6:48
PS: your opening tag <ArticleNo> doesn't match the closing tag </Article_No> - probably just a typo, but be sure those tags match! — marc_s
– marc_s, Commented Aug 15, 2014 at 6:55
Yes, it is a typo, xml automatically generated so there will be no issues with that. — Mardok
– Mardok, Commented Aug 15, 2014 at 7:12

marc_s · Accepted Answer · 2014-08-15 06:54:25Z

3

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.

answered Aug 15, 2014 at 6:54

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mardok Over a year ago

I'm using cursor because I need to check if article exists in DB and if so then update it and insert new in other case. Also I need to insert list of articles (previously get IDs of that articles) to ArticleDepartments table. Of cause it can be done without cursor - just insert result of select into temp table, update that table with department id and update data using "update select from" and "insert select from" statements, but what the difference? As for me cursor easier to read.

marc_s Over a year ago

@Mardok: cursors are notoriously slow and go against all the basics of set-based programming in SQL Server. AVOID cursor whenever you can! Using a temporary table will most likely be much faster than a messy cursor!

Collectives™ on Stack Overflow

Select data from complicated xml in sql server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related