CODEWITHSUNDEEP
sqlsql-serverxml
Asier GR
Asier GR

Reputation: 101

Stored Procedure with a xml data

I am trying to get the data from a field in a SQL server table. It is stored as a xml and I wanted to get each node value in a different row. The xml sample is like this:

<id>{a75f61ce-6627-489f-83bb-d03fc880b764}</id>
<rows>
    <row>
        <columns>
            <column name="ec_date" value="15-November-2017" type="System.DateTime" />
            <column name="ec_amount" value="160" type="System.Decimal" />
            <column name="ec_description" value="viaje en coche" type="System.String" />
            <column name="ec_factura" value="0" type="System.String" />
            <column name="ec_item" value="105.01" type="System.String" />
            <column name="DefaultKey" value="1" type="System.Int32" />
        </columns>
    </row>
    <row>
        <columns>
            <column name="ec_date" value="16-November-2017" type="System.DateTime" />
            <column name="ec_amount" value="2.55" type="System.Decimal" />
            <column name="ec_description" value="2 horas de parkin" type="System.String" />
            <column name="ec_factura" value="0" type="System.String" />
            <column name="ec_item" value="15.04" type="System.String" />
            <column name="DefaultKey" value="2" type="System.Int32" />
        </columns>
    </row>
    <row>
        <columns>
            <column name="ec_date" value="17-November-2017" type="System.DateTime" />
            <column name="ec_amount" value="200" type="System.Decimal" />
            <column name="ec_description" value="taxi a burgos" type="System.String" />
            <column name="ec_factura" value="0" type="System.String" />
            <column name="ec_item" value="15.06" type="System.String" />
            <column name="DefaultKey" value="3" type="System.Int32" />
        </columns>
    </row>
</rows>

Can somebody tell me how to get each < row > element in a sql row with each different column values?

Thank you all

Upvotes: 2

Views: 1209

Answers (2)

StuartLC
StuartLC

Reputation: 107407

In order to parse an Xml column in a table, you'll want to use XQuery. Here's an example of how you would go about this - it will return one row per row xml element:

SELECT
    Rows.col.value('(column[@name="ec_date"]/@value)[1]', 
        'DATE') AS EcDate,
    Rows.col.value('(column[@name="ec_amount"]/@value)[1]', 
        'decimal(10,2)') AS EcAmount,
    Rows.col.value('(column[@name="ec_description"]/@value)[1]', 
        'varchar(max)') AS EcDescription
    -- .. etc
FROM
    MyTable mt
    CROSS APPLY
        mt.XmlCol.nodes('//rows/row/columns') 
      AS Rows(col);

(column[@name="ec_date"]/@value)[1] loosely translates to "find me the first column element with the name attribute of value ec_date and return me the value of the value attribute.

With a Sql Fiddle here

Obligatory Caveats

Although the xml column attribute names and types seem consistent, it seems there is a type system embedded in the data - i.e. an attempt to provide the ability to dynamically change the type of the columns would make this very messy indeed.

On the other hand, of all data in your Xml document has a strong schema, then I would argue that Xml is a poor choice for data modelling - it would have been better to have normalized out row into it's own table. Xml is a verbose format, which repeats the schema (i.e. wastes space), and can be difficult to parse and filter.

Upvotes: 3

Sergey Menshov
Sergey Menshov

Reputation: 3906

I think you can use OPENXML

DECLARE @idoc int, @doc varchar(MAX)

SET @doc='<id>{a75f61ce-6627-489f-83bb-d03fc880b764}</id>
<rows>
    <row>
        <columns>
            <column name="ec_date" value="15-November-2017" type="System.DateTime" />
            <column name="ec_amount" value="160" type="System.Decimal" />
            <column name="ec_description" value="viaje en coche" type="System.String" />
            <column name="ec_factura" value="0" type="System.String" />
            <column name="ec_item" value="105.01" type="System.String" />
            <column name="DefaultKey" value="1" type="System.Int32" />
        </columns>
    </row>
    <row>
        <columns>
            <column name="ec_date" value="16-November-2017" type="System.DateTime" />
            <column name="ec_amount" value="2.55" type="System.Decimal" />
            <column name="ec_description" value="2 horas de parkin" type="System.String" />
            <column name="ec_factura" value="0" type="System.String" />
            <column name="ec_item" value="15.04" type="System.String" />
            <column name="DefaultKey" value="2" type="System.Int32" />
        </columns>
    </row>
    <row>
        <columns>
            <column name="ec_date" value="17-November-2017" type="System.DateTime" />
            <column name="ec_amount" value="200" type="System.Decimal" />
            <column name="ec_description" value="taxi a burgos" type="System.String" />
            <column name="ec_factura" value="0" type="System.String" />
            <column name="ec_item" value="15.06" type="System.String" />
            <column name="DefaultKey" value="3" type="System.Int32" />
        </columns>
    </row>
</rows>'

-- delete <id>
SET @doc=STUFF(@doc,1,47,'')    

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

SELECT *  
FROM OPENXML(@idoc,'/rows/row/columns',2)   
  WITH (ec_date        date         './column[1]/@value',   
        ec_amount      float        './column[2]/@value',   
        ec_description varchar(200) './column[3]/@value',   
        ec_factura     int          './column[4]/@value',   
        ec_item        float        './column[5]/@value',
        DefaultKey     int          './column[6]/@value');

EXEC sp_xml_removedocument @idoc;
GO

I deleted <id>{a75f61ce-6627-489f-83bb-d03fc880b764}</id> first of all.

Upvotes: 1

Related Questions