CODEWITHSUNDEEP
sqlsql-serverxml
davidhlynsson
davidhlynsson

Reputation: 79

Import XML file to SQL table?

I have this code in SQL query:

CREATE TABLE Products(
 P VARCHAR(30));

 Select *
 from products

 Declare @x xml 

 Select @x=P
 from openrowset (BULK 'C:\Pantanir.xml', Single_blob) AS Products(P)  

 Select @x

Where P is an column in table products. With this i only import the XML file into my database but i wont to create a table with something like this, but this does not work.

 from openxml (@hdoc, '/reservation',1)
 with ('Someattribute')

A part of my XML file is shown below. I want to create two columns(ReservationNo and SecurityCode) and take the values from the XML file and import them into an SQL table. Is that possible ?

  <reservation>
    <reservationNo>9833591189</reservationNo>
    <securityCode>ad4badfd56</securityCode>

Upvotes: 1

Views: 343

Answers (1)

Praveen ND
Praveen ND

Reputation: 560

Please try the below code. This is giving the output in SQL Server 2012.

DECLARE @XML XML = ' <reservation>
    <reservationNo>9833591189</reservationNo>
    <securityCode>ad4badfd56</securityCode></reservation>'

SELECT
    reservationNo = Events.value('(reservationNo)[1]', 'varchar(100)'),
    securityCode = Events.value('(securityCode)[1]', 'varchar(100)')
FROM
    @XML.nodes('/reservation') AS XTbl(Events)

Upvotes: 2

Related Questions