XML to table - SQL Server Forums

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

XML to table

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2008-10-27 : 09:54:09

I have a table which contains an xml column that I want to shred to a new table. I have to maintain the IdHusnr, IdPrefixById, IdById, IdAdr and IdPostnr with every product, how do I do this.

Expected output
IdHusnr IdPrefixById IdById IdAdr IdPostnr
1 1 757114 Scheffersgatan 8 11258
1 1 757114 Svedbergsplan 3 11258
2 1 794655 Scheffersgatan 6 11258
2 1 794655 Wennerbergsgatan 6 11258

<IdConstruction>
<IdConstructionRow>
<IdHusnr>1</IdHusnr>
<IdPrefixById>1</IdPrefixById>
<IdById>757114</IdById>
<IdAdress>
<IdAdressRow>
<IdAdr>Scheffersgatan 8</IdAdr>
<IdPostnr>11258</IdPostnr>
</IdAdressRow>
<IdAdressRow>
<IdAdr>Svedbergsplan 3</IdAdr>
<IdPostnr>11258</IdPostnr>
</IdAdressRow>
</IdAdress>
</IdConstructionRow>
<IdConstructionRow>
<IdHusnr>2</IdHusnr>
<IdPrefixById>1</IdPrefixById>
<IdById>794655</IdById>
<IdAdress>
<IdAdressRow>
<IdAdr>Scheffersgatan 6</IdAdr>
<IdPostnr>11258</IdPostnr>
</IdAdressRow>
<IdAdressRow>
<IdAdr>Wennerbergsgatan 6a</IdAdr>
<IdPostnr>11258</IdPostnr>
</IdAdressRow>
</IdAdress>
</IdByggnadRow>
<IdConstruction>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 10:02:53

use this

SELECT v.u.value('./IdHusnr[1]','int') AS IdHusnr,
v.u.value('./IdPrefixById[1]','int') AS IdPrefixById,
v.u.value('./IdById[1]','int') AS IdById,
v.u.value('./IdAdr[1]','varchar(100)') AS IdAdr,
v.u.value('./IdPostnr[1]','int') AS IdPostnr
FROM xmlcol.nodes('/IdConstruction/IdConstructionRow')v(u)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 16:18:26

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112504

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources