How to Import XML file Data into SQLServer Table

Sample XML file error_1.xml

<?xml version="1.0" encoding="UTF-8"?>
<api:response type="failure" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:api="https://audience.job.com/services/flow/ext-register">-<errors>-<error field="passwordCriteria.password" code="regex">
<![CDATA[Password may not contain spaces ]]>
</error>-<error field="emails[0].value" code="emailInvalidFormat">
<![CDATA[Bad email format]]>
</error></errors></api:response>

Query To view the Data from XML file

SELECT  xCol FROM    (SELECT * FROM OPENROWSET (BULK 'E:\error_1.xml',SINGLE_CLOB)  AS xCol) AS R(xCol)

The output  will be as same as xml format file.
--------------------------------------------
<?xml version="1.0" encoding="utf-8"?>  <api:response xmlns:api="https://audience.job.com/services/flow/ext-register" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" type="failure">    <errors>      <error code="regex" field="passwordCriteria.password"><![CDATA[Password may not contain spaces ]]></error>      <error code="emailInvalidFormat" field="emails[0].value"><![CDATA[Bad email format]]></error>    </errors>  </api:response>

If we are converting  the xCol  into XML data type then It will strip or remove the DTD from XML and stored in Table.

I) SELECT CONVERT(xml, BulkColumn)FROM OPENROWSET(Bulk 'E:\error_1.xml', SINGLE_BLOB) [rowsetresults]

II) SELECT  convert(xml,xCol) FROM 
(SELECT * FROM OPENROWSET (BULK 'E:\error_1.xml',SINGLE_CLOB)  AS xCol)
 AS R(xCol)

Output

<api:response xmlns:api="https://audience.job.com/services/flow/ext-register"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" type="failure">
  <errors>
    <error code="regex" field="passwordCriteria.password">Password may not contain spaces </error>
    <error code="emailInvalidFormat" field="emails[0].value">Bad email format</error>
  </errors>

</api:response>