none
Expand all the xml elements into columns

    Frage

  • 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>

    Samstag, 14. April 2012 13:27

Antworten

  • 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


    Samstag, 14. April 2012 13:41

Alle Antworten

  • 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


    Samstag, 14. April 2012 13:41
  • 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


    Samstag, 14. April 2012 13:44
  • 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

    Samstag, 14. April 2012 13:50
  • 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]

    Samstag, 14. April 2012 13:50
  • 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]

    Samstag, 14. April 2012 13:52
  • 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

    Samstag, 14. April 2012 13:53
  • cool mate , thx
    Samstag, 14. April 2012 13:55