How to read an option from XML in T-SQL?

Question

I have the next XML file:

<?xml version="1.0" encoding="utf-8"?>
<DynamicEntity Name="ProductsMilk">
  <Field Name="Name" Type="nvarchar(256)" AllowNulls="false" />
  <Field Name="Description" Type="ntext" AllowNulls="false" />
  <Field Name="Price" Type="float" AllowNulls="false" />
</DynamicEntity>

I need to read some value from any option of XML-node, for e.g. I need to read the value of Type option from Field node using T-SQL.

How can I do this using internal OpenXML provider in MSSQL?

Answer 1

The following will extract all the data from your XML:

DECLARE @doc VARCHAR(1000) = '<?xml version="1.0" encoding="utf-8"?>
    <DynamicEntity Name="ProductsMilk">
      <Field Name="Name" Type="nvarchar(256)" AllowNulls="false" />
      <Field Name="Description" Type="ntext" AllowNulls="false" />
      <Field Name="Price" Type="float" AllowNulls="false" />
    </DynamicEntity>';

DECLARE @iDoc INT;

EXECUTE sp_xml_preparedocument @idoc OUTPUT, @doc;

SELECT  *
FROM    OPENXML(@iDoc, 'DynamicEntity/Field')
        WITH 
        (   DynamicEntityName  VARCHAR(100) '../@Name', 
            FieldName VARCHAR(100) '@Name', 
            [Type] VARCHAR(100) '@Type', 
            AllowNulls VARCHAR(100) '@AllowNulls'
        );

Basically, you just need to specify a column mapping for your xml attributes.

Answer 2

Here is a solution:

DECLARE @x XML = '<?xml version="1.0" encoding="utf-8"?>
<DynamicEntity Name="ProductsMilk">
  <Field Name="Name" Type="nvarchar(256)" AllowNulls="false" />
  <Field Name="Description" Type="ntext" AllowNulls="false" />
  <Field Name="Price" Type="float" AllowNulls="false" />
</DynamicEntity>'


SELECT t.c.value(N'../@Name', N'nvarchar(100)'),
       t.c.value(N'@Name', N'nvarchar(100)'),
       t.c.value(N'@Type', N'nvarchar(100)'),
       t.c.value(N'@AllowNulls', N'nvarchar(100)')
FROM @x.nodes(N'/DynamicEntity/Field') t(c)