泡le沫
2018-06-14 11:36
采纳率: 90.9%
浏览 1.0k

sql server 字符转换问题,急急急

图片说明

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

11条回答 默认 最新

  • wodexinhao 2018-06-14 12:54
    已采纳

    不好意思,刚刚发的太急,有错误,以这个为准
    图片说明

    点赞 评论
  • L_junbao 2018-06-14 11:42

    上面是XML数据格式,
    数据Model
    public class People
    {
    public string NAME { get; set; }
    public string SEX { get; set; }
    public string QQ { get; set; }

    }
        用以下方法从文件中直接读取出对象List<People>  然后就是将对象插入SQLserver数据库对应字段--- 别说这个不会!!
         /// <summary>
        /// 反序列化
        /// </summary>
        /// <returns></returns>
        public static TModel Deserialize<TModel>(string fileName)
        {
            TModel ob;
            if (File.Exists(fileName) == false)
                File.Create(fileName).Close();
            FileStream f = new FileStream(fileName, FileMode.Open);
            XmlSerializer sr = new XmlSerializer(typeof(TModel));
            try
            {
                ob = (TModel)sr.Deserialize(f);
                f.Close();
            }
            catch (Exception e)
            {
                string b = e.Message;
                f.Close();
                return default(TModel);
            }
            return ob;
        }
    
    点赞 评论
  • wodexinhao 2018-06-14 12:31

    use testDb
    declare @myxml xml
    set @myxml='

    dongsheng

    423545


    土豆

    123123


    choushuigou

    54543545

    '

    --获得所有student节点的数据,每一行显示一条people节点的数据

    select @myxml.query('.') as result from @myxml.nodes('/Peoples/people') as T(c)

    --将这些数据显示为一个表格
    select T.c.value('(@id)[1]','int') as id,
    T.c.value('(./Name)[1]','nvarchar(16)') as name,
    T.c.value('(./Sex)[1]','nvarchar(4)') as sex,
    T.c.value('(./QQ)[1]','nvarchar(16)') as QQ
    from @myxml.nodes('/people/student') as T(c)

    点赞 评论
  • wodexinhao 2018-06-14 12:31

    use testDb
    declare @myxml xml
    set @myxml='

    dongsheng

    423545


    土豆

    123123


    choushuigou

    54543545

    '

    --获得所有student节点的数据,每一行显示一条people节点的数据

    select @myxml.query('.') as result from @myxml.nodes('/Peoples/people') as T(c)

    --将这些数据显示为一个表格
    select T.c.value('(@id)[1]','int') as id,
    T.c.value('(./Name)[1]','nvarchar(16)') as name,
    T.c.value('(./Sex)[1]','nvarchar(4)') as sex,
    T.c.value('(./QQ)[1]','nvarchar(16)') as QQ
    from @myxml.nodes('/people/student') as T(c)

    
    
    点赞 评论
  • wodexinhao 2018-06-14 12:31

    这个真不好用,插入代码段,我截图吧

    点赞 评论
  • wodexinhao 2018-06-14 12:34

    图片说明

    点赞 评论
  • chu394548 2018-06-14 12:41

    别人帖子都有写过,可以去看看;
    https://blog.csdn.net/kk185800961/article/details/42277035

    点赞 评论
  • wodexinhao 2018-06-14 13:17

    use demoysc32 这里数据库名字换成你自己的就可以了

    点赞 评论
  • 元辰辰辰辰辰辰 2018-06-14 13:17
    点赞 评论
  • 点赞 评论
  • wodexinhao 2018-06-14 13:23

    刚刚一直有问题,无法显示,现在完整发一遍,麻烦点击一下采纳 ,谢谢

    --把数据库名字 demoysc32 换成你自己的数据库名字就可以了
    use demoysc32 
    declare @myxml xml
    set @myxml='<Peoples>
                    <people>
                        <Name>dongsheng</Name>
                        <Sex>男</Sex>
                        <QQ>423545</QQ>
                    </people>
                    <people>
                        <Name>土豆</Name>
                        <Sex>男</Sex>
                        <QQ>123123</QQ>
                    </people>
                    <people>
                        <Name>choushuigou</Name>
                        <Sex>女</Sex>
                        <QQ>54543545</QQ>
                    </people>
                </Peoples>'
    
    --获得所有student节点的数据,每一行显示一条people节点的数据      
    /*select @myxml.query('.')  as result from @myxml.nodes('/Peoples/people') as  T(c) */
    --将这些数据显示为一个表格
    select  T.c.value('(./Name)[1]','nvarchar(16)') as name,
            T.c.value('(./Sex)[1]','nvarchar(4)') as sex,
            T.c.value('(./QQ)[1]','nvarchar(16)') as QQ
            from @myxml.nodes('/Peoples/people') as T(c)
    

    执行结果,就是你想要的

    图片说明

    点赞 评论

相关推荐 更多相似问题