Stored Procedure with a xml data

Question

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

If the construction of this XML is under your control I'd strongly recommend to use ISO8601 for dates (yyyy-MM-dd or yyyy-MM-ddTHH:mm:ss). Your format is culture and language dependant and might pass all internal tests but break on a customer's system due to a tiny difference like December vs. Dezember — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 21, 2017 at 14:27

StuartLC · Accepted Answer · 2017-11-21 14:28:54Z

3

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.

edited Nov 21, 2017 at 14:28

answered Nov 21, 2017 at 10:46

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

StuartLC Over a year ago

Since there's a 1:1 relationship between 'row' and 'columns, either way works, even with multiple rows of xml data. I guess I've taken the liberty of calling the wrapper 'Rows' in the aliasing, though.

Gottfried Lesigang Over a year ago

Sorry, you are right of course! Voted it up... A tiny hint: You are using /xml in your Xpath, but the original XML in the question does not show the root node. Might be misleading...

StuartLC Over a year ago

Good point - OP's original document didn't have a root and technically isn't valid, but seemingly XQuery was OK with that. Updated.

Sergey Menshov · Accepted Answer · 2017-11-21 10:59:34Z

1

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.

edited Nov 21, 2017 at 10:59

answered Nov 21, 2017 at 10:51

Sergey Menshov

3,9062 gold badges11 silver badges22 bronze badges

2 Comments

Gottfried Lesigang Over a year ago

FROM OPENXML with the corresponding SPs to prepare and to remove a document is outdated and should not be used any more. Rather use the appropriate methods the XML data type provides.

Sergey Menshov Over a year ago

@Shnugo - Thank you for information! I need refresh my knowledge about XML. I have not worked with it for a long time.

Collectives™ on Stack Overflow

Stored Procedure with a xml data

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related