Expand all the xml elements into columns

Question

how can I change this query to expand all the xml elements into columns:

SELECT TOP 40000 [id]
      ,[xmldata]
  FROM [PrepaidCard].[dbo].[TransactionsXML]

id    data
412    <MTID>100</MTID><LOCALDATE>2011-08-10</LOCALDATE><LOCALTIME>115321</LOCALTIME>

Answer 1

SELECT  
 TOP 40000 [id],
[xmldata].value('(/MTID/node())[1]', 'nvarchar(max)') as MTID, 
[xmldata].value('(/LOCALDATE/node())[1]', 'nvarchar(max)') as LOCALDATE,
[xmldata].value('(/LOCALTIME/node())[1]', 'nvarchar(max)') as LOCALTIME

FROM [PrepaidCard].[dbo].[TransactionsXML]

Something like this - I need to actually check the syntax

---

Chuck

Answer 2

Looks like that worked - just change the datatypes in the .value statements from nvarchar(max) to whatever makes sense for the data

DECLARE @temp as TABLE(id int, xmldata xml)
INSERT @temp VALUES(1,'<MTID>100</MTID><LOCALDATE>2011-08-10</LOCALDATE><LOCALTIME>115321</LOCALTIME>')


SELECT  
 TOP 40000 [id],
[xmldata].value('(/MTID/node())[1]', 'nvarchar(max)') as MTID, 
[xmldata].value('(/LOCALDATE/node())[1]', 'nvarchar(max)') as LOCALDATE,
[xmldata].value('(/LOCALTIME/node())[1]', 'nvarchar(max)') as LOCALTIME

FROM @temp

---

Chuck

Answer 3

nice one, is there a way to automatically churn out those 3 extra columns without having to do the

[xmldata].value('(/MTID/node())[1]', 'nvarchar(max)') as MTID, 
[xmldata].value('(/LOCALDATE/node())[1]', 'nvarchar(max)') as LOCALDATE,
[xmldata].value('(/LOCALTIME/node())[1]', 'nvarchar(max)') as LOCALTIME

Answer 4

Try this:

SELECT
	id,
	xmlData.value('(MTID/text())[1]', 'INT') MTID,
	xmlData.value('(LOCALDATE/text())[1]', 'CHAR(10)') LOCALDATE,
	xmlData.value('(LOCALTIME/text())[1]', 'CHAR(8)') LOCALTIME	
FROM [PrepaidCard].[dbo].[TransactionsXML]

Answer 5

thx but say I have elements per node and don't want to write 20 columns, can it be done in 1 line?

SELECT
	id,
	cheat code here?
FROM [PrepaidCard].[dbo].[TransactionsXML]

Answer 6

nice one, is there a way to automatically churn out those 3 extra columns without having to do the

[xmldata].value('(/MTID/node())[1]', 'nvarchar(max)') as MTID, 
[xmldata].value('(/LOCALDATE/node())[1]', 'nvarchar(max)') as LOCALDATE,
[xmldata].value('(/LOCALTIME/node())[1]', 'nvarchar(max)') as LOCALTIME

Not that I know of - because if you think of XML you could have any level of nesting of the tags

---

Chuck

Answer 7

cool mate , thx