带有xml数据的存储过程。

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:

我正在尝试从SQL server表中的一个字段中获取数据。它以xml的形式存储，我希望在不同的行中获得每个节点的值。xml示例如下:

<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?

有人能告诉我如何在sql行中获取每个具有不同列值的< row >元素吗?

Thank you all

谢谢大家

2 个解决方案

#1

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:

为了解析表中的Xml列，您需要使用XQuery。这里有一个例子，说明您将如何操作—它将返回每一行xml元素的一行:

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.

(列[@name="ec_date"]/@value)

With a Sql Fiddle here

这里有一个Sql小提琴

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.

尽管xml列属性名称和类型看起来是一致的，但是似乎在数据中嵌入了一个类型系统——例如，尝试提供动态更改列类型的能力，确实会使这一过程非常混乱。

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.

另一方面，在Xml文档中的所有数据中，都有一个强大的模式，那么我认为对于数据建模来说，Xml是一个糟糕的选择——最好将行规范化为自己的表。Xml是一种冗长的格式，重复模式(即浪费空间)，很难解析和过滤。

#2

I think you can use OPENXML

我认为您可以使用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.

我先删除了 {a75f61ce-6627-489f-83bb-d03fc880b764} 。

#1