XML

常见方法：引用

using System.Xml;
new XmlDocument()  //创建xml返回XmlElement
XmlDocument.CreateElement() //创建节点
XmlDocument.SelectSingleNode();//选中节点
AppendChild(XmlNode);//追加节点
SetAttribute(string name,string value)//节点设置属性
XmlDocument.Load("Url")//读取xml文档
XmlDocument.CreateXmlDeclaration("1.0", "utf-8", "yes");// //创建Xml声明部分，即<?xml version="1.0" encoding="utf-8" ?>
XmlDocument.Save(@"d:\test.xml");//保存文件
XmlDocument.SelectNodes

创建节点

            //常用返回一个xml字符串
            XmlDocument xml = new XmlDocument();
            XmlElement BodyElement = xml.CreateElement("body");
            xml.AppendChild(BodyElement);
            //选择body节点
            XmlElement Student = null;
            XmlNode BodyNode = xml.SelectSingleNode("body");
            Student = xml.CreateElement("Student");
            Student.SetAttribute("name", "qing");
            Student.SetAttribute("age", "18");
            BodyNode.AppendChild(Student);
            string StrXml = BodyNode.OuterXml;
  //<body><Student name="qing" age="18" /></body>


//读取xml加载数据
private void LoadXml()
        {
            XmlDocument xml = new XmlDocument();
            xml.Load(@"d:\test.xml");
            XmlNodeList list = xml.SelectNodes("/Students/Student");
             string  s="";
            if (list != null)
            { 
              foreach(XmlNode node in list)
              {
                  s += node.Attributes["name"].Value;
                  s += node.Attributes["age"].Value;
              }
            }
            MessageBox.Show(s);
        }
//创建xml文件
        private void CreateXml()
        {
            XmlDocument xml = new XmlDocument();
            xml.CreateXmlDeclaration("1.0", "UTF-8", "yes");
            XmlElement root = xml.CreateElement("Students");
            XmlElement Student1 = xml.CreateElement("Student");
            XmlElement grade = xml.CreateElement("grade");
            Student1.SetAttribute("age", "20");
            Student1.SetAttribute("name", "清");
            grade.SetAttribute("Math", "78");
            Student1.AppendChild(grade);
            xml.AppendChild(root);
            root.AppendChild(Student1);
            xml.Save(@"d:\test.xml");
        }

数据库处理xml数据

IF OBJECT_ID('tempdb..#Tem') IS NOT NULL
DROP TABLE #Tem
DECLARE @Xml NVARCHAR(1000);
DECLARE @Pointer INT; 
SET @Xml=N'<body><Student name="qing" age="18"/></body>' --需要加N处理中文
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@Xml;  
  create table #Tem
    (
		Name varchar(30),
		Age varchar(30)
    )
    ;     
    insert into #Tem
    (
		Name,Age
    )        
    select name,age
    FROM   
	OPENXML(@Pointer, '/body/Student') 
	with 
	(
		name varchar(30),
		age varchar(30)
    )
    ;
	EXECUTE sp_xml_removedocument @Pointer;
	SELECT * FROM #Tem

下面是测试

USE [test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Pro_InsertStuent](@Xml NVARCHAR(3000))
AS

DECLARE @Pointer INT; 
--SET @Xml=N'<Body><Student Age="18" Name="小明" /></Body>' 输入参数
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@Xml;  
    
    INSERT INTO  Student
    (
		Name,Age
    )        
    SELECT Name,Age
    FROM   
	OPENXML(@Pointer, '/Body/Student') 
	WITH 
	(
		Name varchar(30),
		Age varchar(30)
    )
    ;
	EXECUTE sp_xml_removedocument @Pointer;
GO

参数说明：OPENXML三个参数第一个是OUTPUT参数，第二个是XPath表达式，第三个是可选项0 1 2 4 8WITH也是可选的，用来指定获取哪些数据字段

如果xml不在结点属性

DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order>
     <OrderID>10248</OrderID>
     <CustomerID>VINET</CustomerID>
     <EmployeeID>5</EmployeeID>
     <OrderDate>1996-07-04T00:00:00</OrderDate>
   </Order>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT *
FROM   OPENXML (@hdoc, '/ROOT/Customer/Order',1)
WITH (OrderID       int         'OrderID',
   CustomerID  varchar(10) 'CustomerID',
   EmployeeID   int    'EmployeeID',
   OrderDate      datetime         'OrderDate')