Post reply

Need help in xml

  • August 13, 2008 at 12:21 am

    #93788

    I have attached the sample xml code for reference. What i want to do is.

    getting the values for each customer in below format

    CustomerID|Name|City|ProjectID|Projectname

    4HardwareshopWashington2Pegasus

    4HardwareshopWashington8Typhon

    i am able to get only customerid by using below query...

    SELECTnref.value('(@CustomerID)[1]', 'int') AS CustomerID

    FROM @xml.nodes('declare namespace E="http://www.w3org.com/CustomersSchema.xsd";/E:Customers') AS R(nref)

    How can i get the above result.

    Abhijit - http://abhijitmore.wordpress.com

  • August 13, 2008 at 5:49 am

    #857312

    Abhijit

    The following should do what you want.

    SELECT customer.value('(@id)[1]', 'int') AS CustomerID

    , customer.value('(name)[1]', 'varchar(50)') AS [Name]

    , customer.value('(city)[1]', 'varchar(50)') AS City

    , project.value('(@id)[1]', 'int') AS ProjectID

    , project.value('(name)[1]', 'varchar(50)') AS Projectname

    FROM @xml.nodes('//customer') AS c (customer)

    CROSS APPLY customer.nodes('projects/project') AS p (project)

    I would suggest checking out the articles on this site by Jacob Sebastian as they answer most standard XML questions and come with very good examples 😀

    Reeth

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply