how to import data from this XML file to Table

Question

Post reply

how to import data from this XML file to Table

parthi-1705

SSCrazy Eights

Points: 9277
More actions
January 29, 2010 at 11:48 pm

#96216

HI
CREATE TABLE Customer (
CustomerId INT PRIMARY KEY,
CompanyName NVARCHAR(20),
City NVARCHAR(20))
and values are in XML like:
ROOT>
<Customers>
<CustomerId>1</CustomerId>
<CompanyName>Name1</CompanyName>
<City>City1</City>
</Customers>
<Customers>
<CustomerId>2</CustomerId>
<CompanyName>Name2</CompanyName>
<City>City2</City>
</Customers>
<Customers>
<CustomerId>3</CustomerId>
<CompanyName>Name3</CompanyName>
<City>City3</City>
</Customers>
</ROOT>
how to import data from this XML file to Customer Table with Query only
Thanks
Parthi

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

To load XML from file into a variable use OPENROWSET:

DECLARE @blob XML;

SELECT @blob = BulkColumn

FROM OPENROWSET(

BULK N'C:\Temp\test.xml'

,SINGLE_BLOB

) t

Parse XML content try XQuery:

DECLARE @Customer TABLE (

CustomerId INT PRIMARY KEY

,CompanyName NVARCHAR(20)

,City NVARCHAR(20)

);

DECLARE @xml XML = '<ROOT>

<Customers>

<CustomerId>1</CustomerId>

<CompanyName>Name1</CompanyName>

<City>City1</City>

</Customers>

<Customers>

<CustomerId>2</CustomerId>

<CompanyName>Name2</CompanyName>

<City>City2</City>

</Customers>

<Customers>

<CustomerId>3</CustomerId>

<CompanyName>Name3</CompanyName>

<City>City3</City>

</Customers>

</ROOT>'

INSERT INTO @Customer

SELECT

T.C.value('(CustomerId)[1]', 'int')

,T.C.value('(CompanyName)[1]', 'nvarchar(20)')

,T.C.value('(City)[1]', 'nvarchar(20)')

FROM @xml.nodes('ROOT/Customers') T(C)

Greets

Flo

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

hi

Nice one it worked out.Say for eg: take this as input of the below query's output it is in XML then how to load this in to my New table which is having ProductID, Name, Color

SELECT ProductID, Name, Color

FROM Production.Product

FOR XML RAW, root('my');

GO

Thanks
Parthi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

What about having a look into BOL for XML methods like "nodes" and "value"? 😎

Tip: FOR XML doesn't work in your case, it works to create XML, not to parse.

Greets

Flo