Dynamic reading xml data in SQL Server

Question

I have a xml data schema like below. All I need is to read the xml data and store it in a temp table. The columns are dynamic (the columns below are samples only), but the attributes are constant. I am expecting the result below.

BandCode BandDescription BandName Code     Country Currency Pay1_11  Pay1_12  Pay1_13  Pay1_14  Pay1_15
123440   Band-A          Band1    1234567  Germany  US      10000.00 10000.00 10000.00 10000.00 10000.00  
123441   Band-B          Band2    1234567  Germany  US      10000.00 10000.00 10000.00 10000.00 10000.00

Sample xml data

<CookedData>
   <row RowNumber="1">
     <Column DestinationColumnCode="BandCode" DataType="VARCHAR(3000)" Value="123440"/>
     <Column DestinationColumnCode="BandDescription" DataType="VARCHAR(3000)" Value="Band-A"/>
     <Column DestinationColumnCode="BandName" DataType="VARCHAR(3000)" Value="Band1"/>
     <Column DestinationColumnCode="Code" DataType="VARCHAR(3000)" Value="1234567"/>
     <Column DestinationColumnCode="Country" DataType="VARCHAR(3000)" Value="Germany"/>
     <Column DestinationColumnCode="Currency" DataType="MONEY" Value="US"/>
     <Column DestinationColumnCode="Pay1_11" DataType="MONEY" Value="10000.00"/>
     <Column DestinationColumnCode="Pay1_12" DataType="MONEY" Value="10000.00"/>
     <Column DestinationColumnCode="Pay1_13" DataType="MONEY" Value="10000.00"/>
     <Column DestinationColumnCode="Pay1_14" DataType="MONEY" Value="10000.00"/>
     <Column DestinationColumnCode="Pay1_15" DataType="MONEY" Value="10000.00"/>
   </row>
   <row RowNumber="2">
     <Column DestinationColumnCode="BandCode" DataType="VARCHAR(3000)" Value="123441"/>
     <Column DestinationColumnCode="BandDescription" DataType="VARCHAR(3000)" Value="Band-B"/>
     <Column DestinationColumnCode="BandName" DataType="VARCHAR(3000)" Value="Band2"/>
     <Column DestinationColumnCode="Code" DataType="VARCHAR(3000)" Value="1234567"/>
     <Column DestinationColumnCode="Country" DataType="VARCHAR(3000)" Value="Germany"/>
     <Column DestinationColumnCode="Currency" DataType="MONEY" Value="US"/>
     <Column DestinationColumnCode="Pay1_11" DataType="MONEY" Value="10000.00"/>
     <Column DestinationColumnCode="Pay1_12" DataType="MONEY" Value="10000.00"/>
     <Column DestinationColumnCode="Pay1_13" DataType="MONEY" Value="10000.00"/>
     <Column DestinationColumnCode="Pay1_14" DataType="MONEY" Value="10000.00"/>
     <Column DestinationColumnCode="Pay1_15" DataType="MONEY" Value="10000.00"/>
   </row>
</CookedData>

I have researched on how to do this, however the sample code that I have found is for static columns only.

The attribute are constant mean that the attribute names are constant or that the attribute values are within a known list? — Serpiton
– Serpiton, Commented Apr 24, 2014 at 11:56
@Serpiton- the attribute name are constant. Sorry for confusion. — user3472262
– user3472262, Commented Apr 25, 2014 at 1:12

Mikael Eriksson · Accepted Answer · 2014-04-24 12:27:58Z

You have to build the query dynamically.

This code uses the information from first row node /CookedData/row[1]/Column to get the column names and datatypes.

This will not work with your XML because you had money specified as the datatype for the column Currency. You need to change that to something else.

declare @SQL nvarchar(max) =
'select '+
  (
  select ',T.X.value(''Column[@DestinationColumnCode = "'+T.ColName+'"][1]/@Value'','''+T.DataType+''') as '+quotename(T.ColName)
  from (
       select T.X.value('@DestinationColumnCode', 'nvarchar(128)') as ColName,
              T.X.value('@DataType', 'nvarchar(128)') as DataType
       from @XML.nodes('/CookedData/row[1]/Column') as T(X)
       ) as T
  for xml path(''), type
  ).value('substring(text()[1], 2)', 'nvarchar(max)')+' '+
'from @XML.nodes(''/CookedData/row'') as T(X)';

exec sp_executesql @SQL, N'@XML xml', @XML;

SQL Fiddle

The dynamically built query looks like this:

select T.X.value('Column[@DestinationColumnCode = "BandCode"][1]/@Value','VARCHAR(3000)') as [BandCode],
       T.X.value('Column[@DestinationColumnCode = "BandDescription"][1]/@Value','VARCHAR(3000)') as [BandDescription],
       T.X.value('Column[@DestinationColumnCode = "BandName"][1]/@Value','VARCHAR(3000)') as [BandName],
       T.X.value('Column[@DestinationColumnCode = "Code"][1]/@Value','VARCHAR(3000)') as [Code],
       T.X.value('Column[@DestinationColumnCode = "Country"][1]/@Value','VARCHAR(3000)') as [Country],
       T.X.value('Column[@DestinationColumnCode = "Currency"][1]/@Value','CHAR(2)') as [Currency],
       T.X.value('Column[@DestinationColumnCode = "Pay1_11"][1]/@Value','MONEY') as [Pay1_11],
       T.X.value('Column[@DestinationColumnCode = "Pay1_12"][1]/@Value','MONEY') as [Pay1_12],
       T.X.value('Column[@DestinationColumnCode = "Pay1_13"][1]/@Value','MONEY') as [Pay1_13],
       T.X.value('Column[@DestinationColumnCode = "Pay1_14"][1]/@Value','MONEY') as [Pay1_14],
       T.X.value('Column[@DestinationColumnCode = "Pay1_15"][1]/@Value','MONEY') as [Pay1_15] 
from @XML.nodes('/CookedData/row') as T(X)

Collectives™ on Stack Overflow

Dynamic reading xml data in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related