Monday, April 29, 2019

XML: How to convert an XML file into a table?

Declare @ContentXML xml
set @ContentXML = '<Root>
  <Fulfil>
    <Id>1</Id>
    <Status>Test</Status>
  </Fulfil>
  <Fulfil>
    <Id>2</Id>
    <Status>Test-2</Status>
  </Fulfil>
  <Fulfil>
    <Id>3</Id>
    <Status>Test-3</Status>
  </Fulfil>
  <Fulfil>
    <Id>4</Id>
    <Status>Test-4</Status>
  </Fulfil>
  <Fulfil>
    <Id>5</Id>
    <Status>Test-5</Status>
  </Fulfil>
</Root>'

Declare @Temp table(Id int, status nvarchar(100))
insert into @Temp
select  pref.value('(Id)[1]','int'),
pref.value('(Status)[1]','nvarchar(100)')
from @ContentXML.nodes('/Root/Fulfil') AS People(pref)

select *, Id * Id as Multiple from @Temp

Table :-

Id status Multiple
1 Test 1
2 Test-2 4
3 Test-3 9
4 Test-4 16
5 Test-5 25

No comments:

Post a Comment