Load XML data into table with T-SQL

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

SQL Server 2008 Forums

Transact-SQL (2008)

Load XML data into table with T-SQL

Author

Topic

mtcoder
Starting Member

19 Posts

Posted - 2010-10-11 : 17:09:01

I have a bunch of xml files that I need to load in. They all look like this

<Root>
    <MuniData>
        <Muni_ID>8</Muni_ID>
        <BuildingPermit>t-343</BuildingPermit>
        <WorkType>XML</WorkType>
        <Address>134 xml</Address>
        <ParcelID> 4343</ParcelID>
        <PermitLink>http://testxml</PermitLink>
        <RequiresExcavation>1</RequiresExcavation>
    </MuniData>
    <MuniData>
        <Muni_ID>8</Muni_ID>
        <BuildingPermit>t-344</BuildingPermit>
        <WorkType>XML</WorkType>
        <Address>1345 xml</Address>
        <ParcelID> 4349</ParcelID>
        <PermitLink>http://testxml</PermitLink>
        <RequiresExcavation>1</RequiresExcavation>
    </MuniData>
</Root>

I need to be able to load them into a table that is structured as such

CREATE TABLE [dbo].[Initial_DataLoad](
    [ID] [bigint] IDENTITY(-999999999,1) NOT NULL,
    [Muni_ID] [int] NOT NULL,
    [BuildingPermit] [varchar](100) NULL,
    [WorkType] [varchar](100) NULL,
    [Address] [varchar](500) NULL,
    [ParcelID] [varchar](50) NULL,
    [PermitLink] [varchar](max) NULL,
    [RequiresExcavation] [bit] NULL,
    [SentToProcessing] [bit] NOT NULL,
    [ProcessedByNorfield] [bit] NOT NULL,
    [SentToHistory] [bit] NOT NULL,
    [TicketNumber] [varchar](12) NULL,
    [TicketDate] [datetime] NULL
) ON [PRIMARY]

The files are stored locally on the server. I just need a way to read and parse the xml into a / the table. I have managed to read the whole xml blob into 1 column, but then am having trouble parsing that column out to all the various values. Ideally, I would like for it to just insert into the table directly via parsing and inserting.

Thank you for the help.

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 01:42:02

[code]
declare @x xml=
'<Root>
<MuniData>
<Muni_ID>8</Muni_ID>
<BuildingPermit>t-343</BuildingPermit>
<WorkType>XML</WorkType>
<Address>134 xml</Address>
<ParcelID> 4343</ParcelID>
<PermitLink>http://testxml</PermitLink>
<RequiresExcavation>1</RequiresExcavation>
</MuniData>
<MuniData>
<Muni_ID>8</Muni_ID>
<BuildingPermit>t-344</BuildingPermit>
<WorkType>XML</WorkType>
<Address>1345 xml</Address>
<ParcelID> 4349</ParcelID>
<PermitLink>http://testxml</PermitLink>
<RequiresExcavation>1</RequiresExcavation>
</MuniData>
</Root>'

select x.i.value('Muni_ID[1]','varchar(40)'),
x.i.value('BuildingPermit[1]','varchar(40)')
---and so on
from @x.nodes('/Root/MuniData')x(i)
[/code]

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources