How to Retrieve Multiple Sibling XML Values - SQL Server XML
There are plenty of examples on how to retrieve a single sibling value. For example given the following XML fragment: 800000 80000 United Security BM 1996 Mountain 21000 2 ISDN 13 Mountain Road Racing Jerseys Jackets Shorts If I want to retrieve the value of “AnnualSales” I can execute the following statement in query analyzer: SELECT Survey_untyped.query('/StoreSurvey/AnnualSales') FROM Stores; The result set will properly display “800000” However, what if I want to retrieve both “AnnualSales” AND “AnnualRevenue”? How would I do that? The query should provide a result set looking like: 800000 80000 Or what if I want three of the sibling values“BankName” in addition to those values? Result set would look like this: 800000 80000 United Security Does anyone know the answer to that?
So your result set would look like this right?? AnnualSales AnnualRevenue BankName 80000 800000 Mybank then why just do this SELECT Survey_untyped.query('/StoreSurvey/AnnualSales') as AnnualSales, Survey_untyped.query('/StoreSurvey/AnnualRevenue') as AnnualRevenue, Survey_untyped.query('/StoreSurvey/BankName') as BankName FROM Stores;