CODEWITHSUNDEEP
sql-serverxml
WebDude
WebDude

Reputation: 6215

mssql convert xml structure into table

I have a user profile stored in xml in a sql database column.
I am trying to build a flat table structure to show this data in a report.
I am using MSSQL 2012.

My structure is dynamic and serialised from a list ofobjects.
In Xml my structure looks like this:

<ArrayOfXmlField>
  <XmlField>
    <Name>FinancialYearEnd</Name>
    <Value>11 February 2015</Value>
  </XmlField>
  <XmlField>
    <Name>Amount</Name>
    <Value>350</Value>
  </XmlField>
  <XmlField>
    <Name>Capturer</Name>
    <Value>Ted Mosby</Value>
  </XmlField>
</ArrayOfXmlField>

The structure may or may not contain all of these fields.
I want the resulting table to turn each XmlField.Name into a column with the XmlField.Value as its row value

Something like:

What i'm after

I have managed to pull this off in a very round about way.
First converting the Xml to a table, the left joining and cherry picking each column.
I have no doubt i'm going to be facing performance issues in no time as well as maintenance when new profile fields are requested.

I have setup an example on SqlFiddle about how i'm currently achieving my goal.
Can anyone steer me in a better direction?
or has my XmlStructure left me with no choices ?

I've found lots of example using attributes, but this object is all node values.

Sql Fiddle Example

Upvotes: 1

Views: 637

Answers (2)

roman
roman

Reputation: 117380

You can simplify your initial query, just pivot the data:

with data as (
    select
        ProfileId,
        vfields.value('(Name)[1]','varchar(500)') as FieldName,
        vfields.value('(Value)[1]','varchar(500)') as FieldValue
    from dbo.[Profile]
        cross apply VarFields.nodes('/ArrayOfXmlField/XmlField') as t(vfields)
    where VarFields is not null
)
select
    ProfileId,
    max(case when FieldName = 'FinancialYearEnd' then FieldValue else '' end) as FinancialYearEnd,
    max(case when FieldName = 'Amount' then FieldValue else '' end) as Amount,
    max(case when FieldName = 'Capturer' then FieldValue else '' end) as Capturer
from data
group by ProfileId

sql fiddle demo

Or just you xpath to query your data:

select
    ProfileId,
    vfields.value('(XmlField[Name = "FinancialYearEnd"]/Value)[1]','varchar(500)') as FinancialYearEnd,
    vfields.value('(XmlField[Name = "Amount"]/Value)[1]','varchar(500)') as Amount,
    vfields.value('(XmlField[Name = "Capturer"]/Value)[1]','varchar(500)') as Capturer
from dbo.[Profile]
    cross apply VarFields.nodes('/ArrayOfXmlField') as t(vfields)

sql fiddle demo

Upvotes: 1

Jeroen Mostert
Jeroen Mostert

Reputation: 28779

You can't construct dynamic result sets through XML -- XPath queries are typed just as much as SQL queries. Although you can do it using dynamic SQL (what problem isn't solvable through dynamic SQL) any solution along those lines will be inefficient as well as hard to incorporate in queries (you can do it in a stored procedure, and nowhere else).

As long as your fields remain known, it isn't hard to produce a table from them, with less code than your linked Fiddle:

SELECT
    ProfileID,
    VarFields.value('(/ArrayOfXmlField/XmlField[Name="FinancialYearEnd"]/Value)[1]', 'nvarchar(500)') AS FinancialYearEnd,
    VarFields.value('(/ArrayOfXmlField/XmlField[Name="Amount"]/Value)[1]', 'nvarchar(500)') AS Amount,
    VarFields.value('(/ArrayOfXmlField/XmlField[Name="Capturer"]/Value)[1]', 'nvarchar(500)') AS Capturer
FROM [Profile]

You don't have to repeat this in queries if you factor out creating the table to a function:

CREATE FUNCTION dbo.ProfileFields(@Fields XML) RETURNS TABLE AS
RETURN
    SELECT 
        @Fields.value('(/ArrayOfXmlField/XmlField[Name="FinancialYearEnd"]/Value)[1]', 'nvarchar(500)') AS FinancialYearEnd,
        @Fields.value('(/ArrayOfXmlField/XmlField[Name="Amount"]/Value)[1]', 'nvarchar(500)') AS Amount,
        @Fields.value('(/ArrayOfXmlField/XmlField[Name="Capturer"]/Value)[1]', 'nvarchar(500)') AS Capturer

And now you can use

SELECT ProfileID, Fields.FinancialYearEnd, Fields.Amount, Fields.Capturer
FROM [Profile] CROSS APPLY dbo.ProfileFields([Profile].VarFields) AS Fields

And you can stick this in a view for further reuse.

This requires adding only two lines when you come up with a new field, but you still have to add those lines. If it's possible for your reporting tool to handle a variable number of fields, you can convert the XML to a name/value table:

SELECT
    ProfileID,
    f.value('Name[1]', 'NVARCHAR(100)') AS FieldName,
    f.value('Value[1]', 'NVARCHAR(500)') AS FieldValue
FROM [Profile]
CROSS APPLY VarFields.nodes('/ArrayOfXmlField/XmlField') AS Fields(f)

Upvotes: 1

Related Questions