Reputation: 101
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
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.
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
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