Query data from an XML field in SQL 2014

Question

Query data from an XML field in SQL 2014

Navigation

#1 by (0 votes)

0

I have an XMLData table. In your Data column I have data like the following:

<?xml version="1.0" encoding="utf-16"?>
<table>
  <id>{6ebd0af9-9838-47f5-8aad-45cf9d4201f7}</id>
  <rows>
    <row>
      <columns>
        <column name="FechaDev" value="01-August-2017" type="System.DateTime" />
        <column name="Importe" value="100" type="System.Decimal" />
        <column name="DefaultKey" value="1" type="System.Int32" />
      </columns>
    </row>
    <row>
      <columns>
        <column name="FechaDev" value="01-September-2017" type="System.DateTime" />
        <column name="Importe" value="200" type="System.Decimal" />
        <column name="DefaultKey" value="2" type="System.Int32" />
      </columns>
    </row>
    <row>
      <columns>
        <column name="FechaDev" value="01-October-2017" type="System.DateTime" />
        <column name="Importe" value="300" type="System.Decimal" />
        <column name="DefaultKey" value="3" type="System.Int32" />
      </columns>
    </row>
  </rows>
  <key>DefaultKey</key>
  <total>0</total>
  <AddOnKey>0</AddOnKey>
  <data />
  <parameters />
</table>

I need to make a query that returns me for example, the amounts that correspond to dates within the month of August, but already to simply list the data I see myself in trouble.

I have tried the following:

select x.ID, 
tabla.columna.value('@FechaDev', 'datetime') as fecha,
tabla.columna.value('@Importe', 'varchar(64)') as importe
from XMLData x
outer apply x.data.nodes('?xml/table/rows/row/columns') as tabla(columna)

But it returns the following error, because it seems that the system does not identify well what is x.data.nodes ...

Mens. 9506, Nivel 16, Estado 1, Línea 5
The XMLDT method 'nodes' can only be invoked on columns of type xml.

I've also tried this:

DECLARE @xml XML
SELECT @xml = Data FROM XMLData
SELECT  
       Tabla.Columna.value('FechaDev', 'datetime') AS Fecha,
       Tabla.Columna.value('Importe', 'VARCHAR(64)') AS Importe
FROM   @xml.nodes('//column') Tabla(Columna)

But it does not work either, it gives me the following error:

Mens. 2389, Nivel 16, Estado 1, Línea 16
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Obviously, something I'm not doing well, any help will be very grateful.

sql xml

asked by sasanca 27.07.2017 в 11:20

source

1 answer

Open a modal form in a separate thread and then close it How to access the value of an input in the Django admin

score 0 · Accepted Answer

Regarding the reading of the xml, one possibility is to "stop" at the node //table/rows/row/columns and in order to access each column in this way:

SELECT  xmlData.Col.value('(column/@value)[1]','varchar(255)') AS 'FechaDev',
    xmlData.Col.value('(column/@value)[2]','varchar(255)') AS 'Importe',
    xmlData.Col.value('(column/@value)[3]','varchar(255)') AS 'DefaultKey'
    FROM    @XML.nodes('//table/rows/row/columns') xmlData(Col)
    WHERE xmlData.Col.value('(column/@value)[1]','varchar(255)') LIKE '%August%'

Note that this assumes that the first column is DateDev, the second is the Amount, Etc. The filter is an example, you may want to convert the values. The final output:

FechaDev       Importe DefaultKey
============== ======= ==========
01-August-2017 100     1

Note: In my case I did not work the encoding utf-16 of <?xml version="1.0" encoding="utf-16"?> I had to modify it to utf-8

I hope you serve