1

I need a help with the situation below:

In my table SQLServer 2012 have a field with xml values, I would like to make a select of the data in that field and show the result in the form columns.

Image1

<row> 
  <ID_Cota>162986</ID_Cota>
  <ID_Taxa_Plano>1000</ID_Taxa_Plano>
  <ID_Plano_Venda>1020</ID_Plano_Venda>
  <ID_Pessoa>18522</ID_Pessoa>
</row>

Thank you.

0

3 Answers 3

7

Use CROSS APPLY and the .nodes method of the XML data-type, eg something like this:

IF OBJECT_ID('dbo.yourTable') IS NOT NULL DROP TABLE dbo.yourTable
GO
CREATE TABLE dbo.yourTable ( 
    rowId INT IDENTITY PRIMARY KEY, 
    ST_Registro_Del XML
)
GO

INSERT INTO dbo.yourTable ( ST_Registro_Del )
SELECT '<row> 
  <ID_Cota>162986</ID_Cota>
  <ID_Taxa_Plano>1000</ID_Taxa_Plano>
  <ID_Plano_Venda>1020</ID_Plano_Venda>
  <ID_Pessoa>18522</ID_Pessoa>
</row>'
GO


SELECT 
    r.c.value('(ID_Cota/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Taxa_Plano/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Plano_Venda/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Pessoa/text())[1]', 'INT' ) AS ID_Cota
FROM dbo.yourTable yt
    CROSS APPLY yt.ST_Registro_Del.nodes('row') r(c)
WHERE rowId = 1

Here are my results:

test results

The .nodes method returns a resultset from XML. Use the text() accessor for a performance boost when querying untyped XML ( ie no XSD is associated with the XML column and the [1] ordinal, which is a shorthand way of saying "get me the first element you find".

HTH

1

Below will help you

declare @sql xml 
set @sql = '<row> 
  <ID_Cota>162986</ID_Cota>
  <ID_Taxa_Plano>1000</ID_Taxa_Plano>
  <ID_Plano_Venda>1020</ID_Plano_Venda>
  <ID_Pessoa>18522</ID_Pessoa>
</row>'

select s.n.value('(ID_Cota)[1]', 'int') as ID_Cota
        ,s.n.value('(ID_Taxa_Plano)[1]', 'int') as ID_Taxa_Plano
        ,s.n.value('(ID_Plano_Venda)[1]', 'int') as ID_Plano_Venda
        ,s.n.value('(ID_Pessoa)[1]', 'int') as ID_Pessoa
from @sql.nodes('/row') as s(n)

enter image description here

1

I would be among the first to say that my xQuery is rusty but the following worked for me:

WITH    xml_string
AS      (
        SELECT val = CAST('<row><ID_Cota>162986</ID_Cota><ID_Taxa_Plano>1000</ID_Taxa_Plano><ID_Taxa_Venda>1020</ID_Taxa_Venda><ID_Pessoa>18522</ID_Pessoa></row>' AS XML)
        )
SELECT  val,
        (val).value('(/row[1]/ID_Cota[1])', 'INT'),
        (val).value('(/row[1]/ID_Taxa_Plano[1])', 'INT'),
        (val).value('(/row[1]/ID_Taxa_Venda[1])', 'INT'),
        (val).value('(/row[1]/ID_Pessoa[1])', 'INT')
FROM    xml_string;

Edited to run query from a table:

WITH    xml_string
AS      (
        SELECT val = CAST('<row><ID_Cota>162986</ID_Cota><ID_Taxa_Plano>1000</ID_Taxa_Plano><ID_Taxa_Venda>1020</ID_Taxa_Venda><ID_Pessoa>18522</ID_Pessoa></row>' AS XML)
        )
SELECT  val
INTO    ST_Registro_Del
FROM    xml_string;

-- run from the table here:
SELECT  val,
        ID_Cota       = (val).value('(/row[1]/ID_Cota[1])', 'INT'),
        ID_Taxa_Plano = (val).value('(/row[1]/ID_Taxa_Plano[1])', 'INT'),
        ID_Taxa_Venda = (val).value('(/row[1]/ID_Taxa_Venda[1])', 'INT'),
        ID_Pessoa     = (val).value('(/row[1]/ID_Pessoa[1])', 'INT')
FROM    ST_Registro_Del
1
  • and how do I use the "ST_Registro_Del" my table :?
    – VAraujo
    Commented May 4, 2016 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.