Iterating through several XML elements? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Iterating through several XML elements?

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-14 : 02:30:56

Hi,
How can I iterate through all the elements in an XML column?

I have this code right now but it doesn't work:

SELECT 
	Faktura.XMLFaktura.Faktura.value('(/FAKTURA/FAKTURA_RADER/RAD/ARTIKELNAMN)[1]', 'varchar(50)') as lev_namn
FROM 
	Faktura.XMLFaktura.nodes('/FAKTURA/FAKTURA_RADER/RAD/') Faktura.XMLFaktura(Faktura)
WHERE faktura_id = 25

I have modified but I can't seem to get it to work. The current error message:

Incorrect syntax near '.'.

The code seems to be identical to this one, which works, except that it declares the XML as an variable:

declare @xml xml
set @xml = '
<Root>
  <ValueHolder>
     <Value>3.00</Value>
     <IsNoteDirty>false</IsNoteDirty>
     <Timestamp>
        <StampType>Month</StampType>
        <Stamp>3</Stamp>
        <Year>2007</Year>
     </Timestamp>
     </ValueHolder>
  <ValueHolder>
     <Value>23.00</Value>
     <IsNoteDirty>false</IsNoteDirty>
     <Timestamp>
        <StampType>Month</StampType>
        <Stamp>3</Stamp>
        <Year>2007</Year>
     </Timestamp>
     </ValueHolder>
</Root>'


select Tab.Col.value('(Value)[1]','MONEY')
from
    @xml.nodes('/Root/ValueHolder') Tab(Col)

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-14 : 03:35:14

I must declare it has a variable?

Now it works:


declare @xmlDoc xml
SET @xmlDoc =(
SELECT Faktura FROM Faktura.XMLFaktura WHERE faktura_id = 25)
	
SELECT 
	Tab.Col.value('(ARTIKELNAMN)[1]', 'varchar(50)') as lev_namn
FROM 
	@xmlDoc.nodes('/FAKTURA/FAKTURA_RADER/RAD') Tab(Col)
--WHERE faktura_id = 25

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 03:38:11

you can iterate through all elemants of xml column as follows


SELECT Tab.Col.value('(Value)[1]','MONEY'),
...
FROM YourTable t
CROSS APPLY XMLColumn.nodes('/Root/ValueHolder') Tab(Col)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources