Read XML node on same level inSQL Server 2008/2005

I have written one article few days back for loading relational data in SQL Server from XML file. I got very good response from that article so I am tempted write another one on XML. Suppose you have different occurrences of data within same level of XML file, how would you query or populate in SQL Server? Let us have a look at it.
–create one variable with XML data
–you can have .XML file saved on your disk
–and can use the same query
DECLARE @xData xml;

SET @xData =
<chem>
   <employee>
    <id>ch001</id>
    <FName>Ritesh</FName>
    <LName>Shah</LName>
    <dept>MIS</dept>
    <dept>Network</dept>
    <dept>DBA</dept>
 </employee>
   <employee>
    <id>ch002</id>
    <FName>Rushik</FName>
    <LName>Shah</LName>
    <dept>Programmer</dept>
    <dept>Developer</dept>
 </employee>
</chem>’
–suppose employee work in more than one department
–how would you list employee with all concern department?
–ofcourse with below give query 😉
SELECT outr.ID,outr.FName, outrmost.y.value(‘text()[1]’,‘varchar(200)’) AS imageUrl
FROM
(
        SELECT
            Inr.i.value(‘id[1]’,‘varchar(20)’) AS ID,
            Inr.i.value(‘FName[1]’,‘varchar(20)’) + ‘ ‘ + Inr.i.value(‘LName[1]’,‘varchar(20)’) AS FName,
            Inr.i.query(‘.’) AS employee_desc
        FROM @xData.nodes(‘/chem/employee’) AS Inr(i)
) AS outr
CROSS APPLY outr.employee_desc.nodes(‘//dept’) AS outrmost(y)
–or you can just list of department based on ID
SELECT outrmost.y.value(‘text()[1]’,‘varchar(200)’) AS imageUrl
FROM
(
        SELECT
            Inr.i.value(‘id[1]’,‘varchar(20)’) AS ID,
            –Inr.i.value(‘FName[1]’,’varchar(20)’) + ‘ ‘ + Inr.i.value(‘LName[1]’,’varchar(20)’) AS FName,
            Inr.i.query(‘.’) AS employee_desc
        FROM @xData.nodes(‘/chem/employee’) AS Inr(i)
) AS outr
CROSS APPLY outr.employee_desc.nodes(‘//dept’) AS outrmost(y)
where outr.ID=‘ch001’
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: