Geek Answers Handbook

How to read parameter from XML in T-SQL?

I have the following XMLfile:

<?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 XML-node, for example. I need to read a Type value from a Node field using T-SQL.

How to do this using the internal OpenXML provider MSSQL?

+4
source share
2 answers

Next, all data from your XML will be extracted:

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.

+2
source

Here is the 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)
+1
source

More articles:


All Articles