Need help on XML urgently
-
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
-
select b.value('@id','int') as CustomerID,b.value('./name[1]','varchar(20)') as Name,
b.value('./city[1]','varchar(20)') as City,
d.value('@id','int') as ProjectID,
d.value('./name[1]','varchar(20)') as Projectname
from @xml.nodes('/customers/customer') as a(b)
outer apply b.nodes('./projects/project') as c(d)
You'll need to resolve any namespace issues yourself
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks for your replu. I already resolved the problem like you have resolved, but i am getting the prob while using namespace
Abhijit - http://abhijitmore.wordpress.com
-
You'll need to post the XML with the namespace declarations
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply