SQL Server XML结果，使用C＃和JavaScriptSerializer（）转换为JSON？

Ok, I'm a little stuck here. I can get my query and return my data as XML. That's not the problem.

好的,我有点卡在这里。我可以获取查询并将数据作为XML返回。那不是问题。

I can create an XDocument. That's not the problem.

我可以创建一个XDocument。那不是问题。

What I would like to be able to do is turn my XML:

我希望能够做的是转换我的XML:

<DATA>
<row>
    <PersonName>Hank Scorpio</PersonName>
</row>
<row>
    <PersonName>Sgt. Pepper</PersonName>
</row>
<row>
    <PersonName>Dr. Sheldon Cooper</PersonName>
</row>
</DATA>

Into JSON. I tried using the dictionary, and something like this:

进入JSON。我尝试使用字典,这样的事情:

var d = xdoc.Descendants("row")
.ToDictionary(val => val.Element("PersonName").Name.LocalName, val => val.Element("PersonName").Value);

But that kept giving me a duplicate value error.

但这一直给我一个重复的值错误。

Then I considered using a List of strings instead of a dictionary. I'm a bit reluctant to do that, though.

然后我考虑使用字符串列表而不是字典。不过,我有点不愿意这样做。

I only have 1189 records to do, but my fear is if it grows too much, even with the given capacity of a list, it may not be the best way to go.

我只有1189条记录要做,但我担心如果它增长太多,即使有一个列表的给定容量,它可能不是最好的方法。

So basically I want to turn the above XML into something like this:

所以基本上我想把上面的XML变成这样的东西:

{
  "DATA": {
    "row": [
      { "PersonName": "Hank Scorpio" },
      { "PersonName": "Sgt. Pepper" },
      { "PersonName": "Dr. Sheldon Cooper" }
    ]
  }
}

Or, to save myself some strife, should I just go ahead and do the list? For the application I am building this for, I am sending the values to an API, but they're currently deciding if they also want an ID for the record. So I'm trying to figure out a solution that won't require a large re-write should they change their minds later.

或者,为了省去一些冲突,我应该继续做清单吗?对于我正在构建此应用程序的应用程序,我将值发送给API,但他们目前正在决定是否还需要记录的ID。所以我试图找出一个解决方案,如果他们以后改变主意,就不需要大量的重写。

We're using VS2015, so I don't think we have some of the newer C# features.

我们正在使用VS2015,所以我认为我们没有一些较新的C#功能。

Any thoughts or ideas?

有什么想法或想法吗?

2 个解决方案

#1

In your code you are trying to convert the contents of all the individual rows into a single dictionary. That won't work because all the xml row contents have the same key, "PersonName", whereas dictionary keys must be unique.

在您的代码中,您尝试将所有单个行的内容转换为单个字典。这不起作用,因为所有xml行内容都具有相同的键“PersonName”,而字典键必须是唯一的。

What you really want to do is convert each xml row into its own dictionary and then put those dictionaries into a list. That is essentially what your desired JSON represents, along with a couple of wrapper objects.

您真正想要做的是将每个xml行转换为自己的字典,然后将这些字典放入列表中。这基本上是您所需的JSON代表,以及几个包装器对象。

With that in mind, here is how you would convert your XML to your desired JSON:

考虑到这一点,以下是将XML转换为所需JSON的方法:

var xdoc = XDocument.Parse(xml);

var obj = new
{
    DATA = new
    {
        row = xdoc.Root
                  .Elements("row")
                  .Select(r => r.Elements()
                                .ToDictionary(el => el.Name.LocalName, 
                                              el => el.Value))
                  .ToList()
    }
};

JavaScriptSerializer jss = new JavaScriptSerializer();
string json = jss.Serialize(obj);

#2

This is what I used to get my serialized JSON:

这是我用来获取序列化JSON的方法:

var xdoc = XDocument.Parse(queryResult, LoadOptions.PreserveWhitespace);

            var elementList = xdoc.Descendants("row").Select(r => new
            {
                PersonID = r.Element("PersonID").Value,
                PersonName = r.Element("PersonName").Value
            }).Distinct().ToList();

            jsonResult = new JavaScriptSerializer().Serialize(elementList);

And my deserialization looked like this:

我的反序列化看起来像这样:

dynamic deserializedJson = new JavaScriptSerializer().Deserialize<dynamic>(result);

And I iterated over it like this:

我像这样迭代它:

foreach (Dictionary<string, object> d in deserializedJson)
            {
                Console.WriteLine(d["PersonName"]);
            }

#1