davidhlynsson davidhlynsson - 6 months ago 10
SQL Question

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>

Answer

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)