davidhlynsson davidhlynsson - 2 years ago 76
SQL Question

Import XML file to SQL table?

I have this code in SQL query:


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 ?


Answer Source

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

DECLARE @XML XML = ' <reservation>

    reservationNo = Events.value('(reservationNo)[1]', 'varchar(100)'),
    securityCode = Events.value('(securityCode)[1]', 'varchar(100)')
    @XML.nodes('/reservation') AS XTbl(Events)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download