SQL Server read xml file data by OPENROWSET

Question

CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;

SELECT * FROM XMLwithOpenXML

1) what is BulkColumn ?

Second Code example

DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x

SELECT
    t.c.value('(PersonId/PersonNr/text())[1]', 'VARCHAR(100)'),
    t.c.value('(Namn/Tilltalsnamnsmarkering/text())[1]', 'INT')
FROM @xml.nodes('*:ArrayOfFolkbokforingspostTYPE/*:FolkbokforingspostTYPE/*:Personpost') t(c)

1) OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x
What is first parameter called BULK ?
X alias is required ?

Another Code

INSERT INTO CUSTOMERS_TABLE (DOCUMENT, NAME, ADDRESS, PROFESSION)
SELECT
   MY_XML.Customer.query('Document').value('.', 'VARCHAR(20)'),
   MY_XML.Customer.query('Name').value('.', 'VARCHAR(50)'),
   MY_XML.Customer.query('Address').value('.', 'VARCHAR(50)'),
   MY_XML.Customer.query('Profession').value('.', 'VARCHAR(50)')
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\temp\MSSQLTIPS_XML.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('Customers/Customer') AS MY_XML (Customer);

1) CAST(MY_XML AS xml) what is stored in MY_XML variable ? because alias mention as T(MY_XML)
2) why cross apply is required ? without cross apply we can extract data.

3) to use OPENROWSET any special permission would be required ?

please guide me. thanks

Answer 1

1) what is BulkColumn ?

OPENROWSET returns one column and the name of the column is "BulkColumn"

X alias is required ?

Yes, a table alias is mandatory

1) CAST(MY_XML AS xml) what is stored in MY_XML variable ? because alias mention as T(MY_XML)

It's not a variable, T is the table alias and MY_XML is a column alias for the result set

why cross apply is required

There is no relation direct relation between the result, where you can use a OUTER/INNER JOIN, so a CROSS JOIN is used.

to use OPENROWSET any special permission would be required

See https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15#permissions

Answer 2

Hi @Sudip Bhatt ,

Adding some notes to Olaf's answer:

What is first parameter called BULK ?

You use BULK INSERT or OPENROWSET(BULK...) to import data to SQL Server.

The OPENROWSET bulk rowset provider is accessed by calling the OPENROWSET function and specifying the BULK option.

You could consider BULK inside OPENROWSET is a fixed collocation.

Reference: OPENROWSET(BULK...) Function

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table