SQL or C#: Loop through values of one field and insert into another

This is a value in a field called DataFields of a table called objects:

<data><styleid>287634</styleid><c1001>S</c1001><c1002>S</c1002><c1004>S</c1004></data>

I need to do this:

Select into objectsindex (product, typeid, classid, objectid, FieldName, FieldValue)

Values

select
product,
typeid,
classid,
objectid,
FieldName = 'c1001',
FieldValue = CONVERT(xml, DataFields).value('(/data/c1001/node())[1]', 'nvarchar(1)')
from objects where typeid = 45

for each XML node in that field (besides the <data></data> and <styleid></styleid> nodes) and for each record of the objects table where typeid = 45.

The 'c1001' and /c1001/ values are variable need to be pulled from the DataFields field.

I put c# in the title because I assume many of you will tell me this isn't a job for SQL alone. But I know there are some real SQL Geniuses out there so I'开发者_如何学运维m hoping for a SQL solution.

XQuery to the rescue! Try this -

DECLARE @X XML = '<data><styleid>287634</styleid><c1001>S</c1001><c1002>S</c1002><c1004>S</c1004></data>';

WITH T AS (
  SELECT CONVERT(VarChar(100), X.query('local-name(.)')) NodeName,
         X.value('.', 'VarChar(100)') NodeValue
  FROM @X.nodes('//*') F(X)
)
SELECT *
FROM T
WHERE NodeName LIKE 'C%';

That will get your data, and from there I think the INSERT should be trivial. =)

..and since you mentioned C#, here is how you would do it with a LINQ snippet.

You can pick up LINQPad (free at http://www.linqpad.net) and run this directly without creating a whole new project for it.

var objects45 = Objects.Where(obj=>obj.Typeid=="45");
foreach(var obj in objects45) {
    var xml = XElement.Parse(obj.Datafields);
    var fields = xml.Elements().Where(e=>e.Name != "styleid");
    var newRecords = from fieldTag in xml.Elements()
                    where fieldTag.Name != "styleid"
                    select new ObjectsIndex() {
                        Product = obj.Product,
                        Typeid = obj.Typeid,
                        Classid = obj.Classid,
                        Objectid = obj.Objectid,
                        Fieldname = fieldTag.Name.LocalName,
                        Fieldvalue = fieldTag.Value
                    };
    newRecords.Dump("These records will be inserted:");
    // Uncomment to actually insert
    // ObjectsIndexes.InsertAllOnSubmit(newRecords);
}
// Uncomment to actually insert
// ObjectsIndexes.Context.SubmitChanges();

declare @XML xml = 
'<data>
   <styleid>287634</styleid>
   <c1001>S</c1001>
   <c1002>S</c1002>
   <c1004>S</c1004>
 </data>'

declare @T table (TypeID int, XMLCol xml)
insert into @T values (45, @XML)
insert into @T values (46, @XML)

select
  T.TypeID,
  C.Name,
  D.Item.value('.', 'varchar(max)') as Value
from @T as T
  cross apply T.XMLCol.nodes('/data/*') as D(Item)
  cross apply (select D.Item.value('local-name(.)', 'varchar(max)')) as C(Name)
where
  C.Name <> 'styleid' and
  T.TypeID = 45

Result

TypeID  Name   Value
45      c1001  S
45      c1002  S
45      c1004  S

Another version

select
  T.TypeID,
  D.Item.value('local-name(.)', 'varchar(max)') as Name,
  D.Item.value('.', 'varchar(max)') as Value
from @T as T
  cross apply T.XMLCol.nodes('/data/*[local-name(.)!="styleid"]') as D(Item)
where
  T.TypeID = 45