Import XML file to SQL table?

Question

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>

Praveen ND · Accepted Answer · 2016-05-18 09:31:04Z

2

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)

answered May 18, 2016 at 9:31

Praveen ND

5602 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import XML file to SQL table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related