xml to sql

Example1 on how to read xml in sql server

declare @xmlfullstring xml

set @xmlfullstring= '<root>

<name>madhan</name>

<name>kumar</name>

</root>'

select xfs.name.value('name[1]','varchar(50)') as namexml,

xfs.name.value('name[2]','varchar(50)') as namexml

from @XmlFullString.nodes('root')xfs(name)

Example2 on how to read xml in sql server

DECLARE @Details xml

    SET @Details = '<root>

    <SelectedCategory CategoryId="101">

        <ItemID>120</ItemID>

        <ItemID>256</ItemID>

      </SelectedCategory>

      <SelectedCategory CategoryId="125">

        <ItemID>158</ItemID>

        <ItemID>120</ItemID>

    </SelectedCategory>

    <SelectedCategory CategoryId="15">

            <ItemID>5986</ItemID>

            <ItemID>20</ItemID>

        <ItemID>268</ItemID>

      </SelectedCategory>

    </root>'

    SELECT  T.[CategoryID],

            T2.Loc.query('.').value('.', 'INT') Val

    FROM

        ( SELECT SelCat.CatDet.value('(@CategoryId)[1]', 'int') as CategoryID,

                 SelCat.CatDet.query('.') as ItemID

          FROM @Details.nodes('/root/SelectedCategory') as SelCat(CatDet)

         ) T  CROSS APPLY ItemID.nodes('/SelectedCategory/ItemID') as T2(Loc)

Example3 on how to read xml in sql server

declare @studentxml xml ='

<Students>

<Student>

<Id>1</Id>

<Name>Madhan</Name>

</Student>

      <Student>

<Id>2</Id>

<Name>Karthi</Name>

</Student>

      <Student>

<Id>3</Id>

<Name>Bata</Name>

</Student>

</Students>'

SELECT X.Student.query('Id').value('.', 'INT') as studentid,

       X.Student.query('Name').value('.', 'VARCHAR(50)') as studentname

FROM @studentxml.nodes('/') as a(b)

CROSS APPLY b.nodes('Students/Student') AS X(Student);