ADD XML data and structure into SQL table

See more:

XML

SQL

Hi

I would like to transform the XML below into a SQL table. The column names are the schema names on the XML. for instance one column of the sql table will be 'Customer type.
Please can you provide me a sql script to do this ?



XML data

<caseresponse xmlns="http://www.aptean.com/respond/caseresponse/2" version="2">
  <case name="kibrom, Ms Karren" id="KibromsData" tag="">
    <field type="Category" schemaname="Case.CustomerType" name="Customer Type">
    <field type="Action" schemaname="Case.LastActionId" name="Last Action">
      <value id="30612">Close
    
    <field type="DateTime" schemaname="Case.OnHoldWarningDate" name="On Hold Warning Date">
      <value>
    
    <field type="Integer" schemaname="Case.WorkingDaysToResolve" name="Working Days To Resolve">
      <value>38
    
    <field type="Integer" schemaname="Case.DerivedElapsedHoursToResolve" name="Derived Elapsed Hours To Resolve">
      <value>1344
    
    <field type="Category" schemaname="Case.ReceivedFrom" name="Received From">
    <field type="DateTime" schemaname="Case.ResolvedDate" name="Resolved Date">
      <value>2023-04-11T15:16:00

EDIT: OP's response to a question - both posted as solutions
I am using Microsoft SQL 2014. Yes i would like to create a table with columns from the XML and store the values.

Transform the XML into SQL table. The column for the SQL table will be the schema name in the XML tag

What I have tried:

Have tried to use

EXEC sys.sp_xml_preparedocument @idoc OUTPUT, @param_XML

but it didn't work.

I am not sure how can i extract the Schema name to the OpenXML script shown below:

SELECT text FROM OPENXML (@idoc