CODEWITHSUNDEEP
sqlxmlsql-server-2005xml-parsing
angel
angel

Reputation: 4632

How to read a xml in SQL Server 2005?

I have this query

WITH XMLNAMESPACES (DEFAULT 'http://www.w3.org/2005/Atom') 
select 
cast(idsku as int) idsku,
cast(entregado as int) entregado from #final1 final FOR XML AUTO, ROOT ('clientesxml'), ELEMENTS

this return 

<clientesxml xmlns="http://www.w3.org/2005/Atom">
  <final>
    <idsku>191</idsku>
    <entregado>159</entregado>
  </final>
</clientesxml>

My question how do I create a stored procedure which reads the XML returned and converts it into a #tempTable ?

Upvotes: 2

Views: 329

Answers (1)

Tim Lehner
Tim Lehner

Reputation: 15251

Here are a few similar methods that may come in handy when creating your proc:

-- Declare some xml
declare @xml xml = '<clientesxml xmlns="http://www.w3.org/2005/Atom">
  <final>
    <idsku>191</idsku>
    <entregado>159</entregado>
  </final>
</clientesxml>'

-- Shred the xml
;with xmlnamespaces (default 'http://www.w3.org/2005/Atom') 
select x.Record.value('idsku[1]', 'int') as idsku
    , x.Record.value('entregado[1]', 'int') as entregado
from @xml.nodes('//clientesxml/final') as x (Record)

-- Shred and insert the xml into a new temp table
;with xmlnamespaces (default 'http://www.w3.org/2005/Atom') 
select x.Record.value('idsku[1]', 'int') as idsku
    , x.Record.value('entregado[1]', 'int') as entregado
into #tempTable
from @xml.nodes('//clientesxml/final') as x (Record)

-- Shred and insert the xml into an existing temp table
;with xmlnamespaces (default 'http://www.w3.org/2005/Atom') 
insert into #tempTable (idsku, entregado)
select x.Record.value('idsku[1]', 'int') as idsku
    , x.Record.value('entregado[1]', 'int') as entregado
from @xml.nodes('//clientesxml/final') as x (Record)

Upvotes: 1

Related Questions