xml column with 40 attributes - how to dynamically retreive all of them in tsql

Question

Post reply

xml column with 40 attributes - how to dynamically retreive all of them in tsql

praveena challa

Grasshopper

Points: 14
More actions
September 28, 2010 at 1:56 pm

#228847

hi ,
any help is gr8tly appreciated.
I have an XML column which looks like below - all i want to do is somethinf like select * from Xmlcolumn (wihtout specifying the attribute names)and I want the attribtues to be seperated as column names
sample code is
<EmailBatch>
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="EmailBatch">
<xs:element name="EmailBatch" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<EmailDetail dealerid="51" OpportunityID="74892" ClientName="Martha Hart" saleid="255049" email="JP@YAHOO.COM" />
<EmailDetail dealerid="51" OpportunityID="98802" ClientName="Timothy Lowry" saleid="255273" email="karen@hinds.com" />
</EmailBatch>
the problem is I cannot specify the column names and retreive them - I need all the attribtues wihtout mentioning their names:
SELECT distinct
Batch.client.value('./@DealerID', 'nvarchar(max)') as DID,
Batch.client.value('./@OpportunityID', 'nvarchar(max)') as OpportunityID,
Batch.client.value('./@Clientname', 'nvarchar(max)') as Clientname,
Batch.client.value('./@SaleID', 'nvarchar(max)') as SaleID,
Batch.client.value('./@Email', 'nvarchar(max)') as Email
FROM @doc.nodes('/EmailBatch/EmailDetail') Batch(client)
WHERE Batch.client.value('./@dealerid', 'nvarchar(max)') IN (SELECT dealerid FROM EMAILDETAIL WHERE EMAILBATCHID = 121
(this works perfectly fine - but I am not allowed to hardcode column names.)
I want the output as follows:
DealerID OpportunityID ClientName Saleid email
------------------------------------------------------------
51 74892 Martha Hart 255049 JP@YAHOO.COM
51 98802 Timo L 255273 karen@hinds.com
please help
thanks in advance
pivi

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

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

Check out this article: Select * from XML[/url] - it seems like it would do what you're asking for.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

praveena challa Grasshopper Points: 14 More actions · Answer 2

thanks for taking out time to reply - but unfortunately that example doesnt solve my problem

because I have conditional checks to do based on one of the fields and xmltable function displays all attributes as rows

whereas i need them displayed as columns

thank you once again