loading xml data into table in SQL Server 2012

You can use OPENXML.

Following Arshad Ali's example:

Process XML data using OPENXML function

Now as I said before, XML data stored in a column of data type XML can be processed either by using XML functions available in SQL Server or by using the sp_xml_preparedocument stored procedure along with the OPENXML function.

We will first call the sp_xml_preparedocument stored procedure by specifying the XML data which will then output the handle of the XML data that it has prepared and stored in internal cache.

Then we will use the handle returned by the sp_xml_preparedocument stored procedure in the OPENXML function to open the XML data and read it.

Note: the sp_xml_preparedocument stored procedure stores the XML data in SQL Server's internal cache, it is essential to release this stored XML data from internal cache by calling the sp_xml_removedocument stored procedure. We should call the sp_xml_removedocument stored procedure as early possible, so that internal cache can be freed for other usage.

USE OPENXMLTesting
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT CustomerID, CustomerName, Address
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer')
WITH 
(
CustomerID [varchar](50) '@CustomerID',
CustomerName [varchar](100) '@CustomerName',
Address [varchar](100) 'Address'
)

EXEC sp_xml_removedocument @hDoc
GO

Had the same issue, so, I hope this will help you:

DECLARE @idoc int, @doc nvarchar(max); 
SET @doc ='
<Validations>
    <Validation>
        <validation id="ActivityIdPass" status="Fail">An Activity ID must be defined.</validation>
        <validation id="ActivityFormatPass" status="Pass">Passed</validation>
        <validation id="ActivityFormatSubCatPass" status="Pass">Passed</validation>
        <validation id="StartDateTimePass" status="Fail">A Release Date must be defined</validation>
        <validation id="EndDateTimePass" status="Fail">A Expiration Date must be defined</validation>
        <validation id="CityPass" status="Pass">Passed</validation>
        <validation id="State" status="Pass">Passed</validation>
    </Validation>
</Validations>'; 

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; 

select ActivityIdPass, ActivityFormatPass, ActivityFormatSubCatPass, StartDateTimePass, EndDateTimePass, CityPass, [State]
from
(
SELECT id,val_id,val_text
FROM OPENXML (@idoc, '/Validations/Validation/validation',2) 
         WITH (id       int          '@mp:parentid', 
               val_id   nvarchar(50) '@id', 
               val_text nvarchar(50)  '.')
) d
pivot
(
  max(val_text)
  for val_id in (ActivityIdPass, ActivityFormatPass, ActivityFormatSubCatPass, StartDateTimePass, EndDateTimePass, CityPass, [State])
) piv;

EXEC sp_xml_removedocument @idoc

This snippet does it using XQuery.

• Using XML.nodes() to gather the Validations/Validation nodes using an XQuery
• Using XML.value() to extract the value from the <validation></validation> elements

DECLARE @inp XML='
<Validations>
    <Validation>
        <validation id="ActivityIdPass" status="Fail">An Activity ID must be defined.</validation>
        <validation id="ActivityFormatPass" status="Pass">Passed</validation>
        <validation id="ActivityFormatSubCatPass" status="Pass">Passed</validation>
        <validation id="StartDateTimePass" status="Fail">A Release Date must be defined</validation>
        <validation id="EndDateTimePass" status="Fail">A Expiration Date must be defined.</validation>
        <validation id="CityPass" status="Pass">Passed</validation>
        <validation id="State" status="Pass">Passed</validation>
    </Validation>
</Validations>';

SELECT
    n.v.value('(validation[@id="ActivityIdPass"])[1]','NVARCHAR(128)') AS ActivityIdPass,
    n.v.value('(validation[@id="ActivityFormatPass"])[1]','NVARCHAR(128)') AS ActivityFormatPass,
    n.v.value('(validation[@id="ActivityFormatSubCatPass"])[1]','NVARCHAR(128)') AS ActivityFormatSubCatPass,
    n.v.value('(validation[@id="StartDateTimePass"])[1]','NVARCHAR(128)') AS StartDateTimePass,
    n.v.value('(validation[@id="EndDateTimePass"])[1]','NVARCHAR(128)') AS EndDateTimePass,
    n.v.value('(validation[@id="CityPass"])[1]','NVARCHAR(128)') AS CityPass,
    n.v.value('(validation[@id="State"])[1]','NVARCHAR(128)') AS State
FROM
    @inp.nodes('/Validations/Validation') AS n(v);

Result:

+---------------------------------+--------------------+--------------------------+--------------------------------+------------------------------------+----------+--------+
|         ActivityIdPass          | ActivityFormatPass | ActivityFormatSubCatPass |       StartDateTimePass        |          EndDateTimePass           | CityPass | State  |
+---------------------------------+--------------------+--------------------------+--------------------------------+------------------------------------+----------+--------+
| An Activity ID must be defined. | Passed             | Passed                   | A Release Date must be defined | A Expiration Date must be defined. | Passed   | Passed |
+---------------------------------+--------------------+--------------------------+--------------------------------+------------------------------------+----------+--------+