Querying XML data using XQuery in SQL SERVER

In many cases we have to pass xml data as parameter to our stored procedures

And it will be very easy to handle these xml data if we can query it like normal sql tables.

Here I am explaining about how to Query XML values in Tsql.

Example 1:

 Below is a sample xml data that holds the address of a user

DECLARE @xmldata XML = '<Root>

    <Address>

    <Housename>Valiyaveetil</Housename>

    <Place>Alappuzha</Place>

    <pin>688539</pin>

    </Address>

    <Address>

    <Housename>Chaithram</Housename>

    <Place>Ernakulam</Place>

      <pin>688552</pin>

    </Address>

    <Address>

    <Housename>Prasanthi</Housename>

    <Place>Kottayam</Place>

      <pin>688536</pin>

    </Address>

    <Address>

    <Housename>RajBhavanam</Housename>

    <Place>Kollam</Place>

      <pin>688529</pin>

    </Address>

    </Root>'

We can use the following query to parse the xml look like a table format.

SELECT Housename = Adr.value('(Housename)[1]', 'nvarchar(max)')

      ,Place = Adr.value('(Place)[1]', 'nvarchar(max)')

      ,Pin = Adr.value('(pin)[1]', 'nvarchar(max)')

FROM @xmldata.nodes('/Root/Address') AS Adddress(Adr)

The result of the above query will be like below.

Housename              Place              Pin

-----------------------------------------------------------

Valiyaveetil              Alappuzha      688539

Chaithram                Ernakulam      688552

Prasanthi                  Kottayam        688536

RajBhavanam          Kollam             688529

Example 2:

In  our below example we are discussing about a different scenario, here we have a common node called Common value that is common for all other nodes, state and country are common for all users. Here I am explaining  about how we can query the xml which contains common values like below .

DECLARE @xmldatas XML = '<Root>

<CommonValue>

<state>kerala</state>

<Country>India</Country>

</CommonValue>

    <Address>

    <Housename>Valiyaveetil</Housename>

    <Place>Alappuzha</Place>

    <pin>688539</pin>

    </Address>

    <Address>

    <Housename>Chaithram</Housename>

    <Place>Ernakulam</Place>

      <pin>688552</pin>

    </Address>

    <Address>

    <Housename>Prasanthi</Housename>

    <Place>Kottayam</Place>

      <pin>688536</pin>

    </Address>

    <Address>

    <Housename>RajBhavanam</Housename>

    <Place>Kollam</Place>

      <pin>688529</pin>

    </Address>

    </Root>'

The query will be like below :

SELECT Housename = Adr.value('(Housename)[1]', 'nvarchar(max)')

,Place = Adr.value('(Place)[1]', 'nvarchar(max)')

,Pin = Adr.value('(pin)[1]', 'nvarchar(max)')

,[state] = cmnvl.value('(state)[1]', 'nvarchar(max)')

,[Country] = cmnvl.value('(Country)[1]', 'nvarchar(max)')

FROM @xmldatas.nodes('/Root/Address') AS Adddress(Adr)

CROSS APPLY @xmldatas.nodes('/Root/CommonValue') AS Commonvalues(cmnvl)

It results :

Housename              Place             Pin                  state                Country

-----------------------------------------------------------------------------------------------

Valiyaveetil             Alappuzha     688539             kerala               India

Chaithram                Ernakulam     688552            kerala               India

Prasanthi                  Kottayam       688536            kerala               India

RajBhavanam          Kollam           688529            kerala               India