将多个表转换为xml文件
本文关键字：xml 文件 转换 | 更新日期: 2023-09-27 18:10:04
我在Sql Server中有这个数据，我需要转换为XML文件，每个客户一个。我有2个sql查询，客户和消息。
我还有一个作为要求提供的XSD文件。
生成这个特定XML文件的最有效方法是什么?
UPDATE:所谓高效，我指的是处理速度快，或者代码简单。我不太关心速度，因为它们是小文件。谢谢。
这是给我的示例xml:
<?xml version="1.0" encoding="UTF-8"?>
<Program xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="program.xsd">
<Customer>
<CId>6674</CId>
<ClientName>c1</ClientName>
<SubscriberId>1234</SubscriberId>
</Customer>
<CreatedOn>2014-06-17T19:09:53.960</CreatedOn>
<Message>
<MessageId DeliveryChannel="1">123456</MessageId>
<Prospect>
<Id>12345678</Id>
</Prospect>
<SentDate>2014-06-24T12:00:01</SentDate>
<CName>x1</CName>
<CNameId>1234</CNameId>
</Message>
<Message>
<MessageId DeliveryChannel="2">1236457</MessageId>
<Prospect>
<Id>12345679</Id>
</Prospect>
<SentDate>2014-06-24T12:00:02</SentDate>
<CName>x2</CName>
<CNameId>1235</CNameId>
</Message>
</Program>
模式:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="Program">
<xs:complexType>
<xs:sequence>
<xs:element ref="Customer" minOccurs="1" maxOccurs="unbounded"/>
<xs:element name="CreatedOn" type="xs:dateTime" minOccurs="1" maxOccurs="1"/>
<xs:element ref="Message" minOccurs="1" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Customer">
<xs:complexType>
<xs:sequence>
<xs:element name="CId" type="xs:string" minOccurs="1" maxOccurs="1"/>
<xs:element name="ClientName" type="xs:string" minOccurs="1" maxOccurs="1"/>
<xs:element name="SubscriberId" type="xs:integer" minOccurs="1" maxOccurs="1"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Message">
<xs:complexType>
<xs:sequence>
<xs:element ref="MessageId" minOccurs="1" maxOccurs="1"/>
<xs:element ref="Prospect" minOccurs="1" maxOccurs="1"/>
<xs:element name="SentDate" type="xs:dateTime" minOccurs="1" maxOccurs="1"/>
<xs:element name="CName" type="xs:string" minOccurs="1" maxOccurs="1"/>
<xs:element name="CNameId" type="xs:integer" minOccurs="0" maxOccurs="1"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Prospect">
<xs:complexType>
<xs:sequence>
<xs:element ref="Id" minOccurs="1" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Id">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="Source" type="xs:string" default="default"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:simpleType name="DeliveryChannel">
<xs:restriction base="xs:integer">
<xs:enumeration value="1"/>
<xs:enumeration value="2"/>
<xs:enumeration value="4"/>
</xs:restriction>
</xs:simpleType>
<xs:element name="MessageId">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:integer">
<xs:attribute name="DeliveryChannel" type="DeliveryChannel"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:schema>
你是否愿意学习另一种语言?VB。Net有一些非常好的内置XML支持，可以生成有效的XML，而不必过多地摆弄字符串:
' Customers would be a database context of some form
For Each C In Customers
Dim MyData = <?xml version="1.0" encoding="UTF-8"?>
<Program xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="program.xsd">
<Customer>
<CId><%= C.CId %></CId>
<ClientName><%= C.ClientName %></ClientName>
<SubscriberId><%= C.SubscriberId %></SubscriberId>
</Customer>
<CreatedOn><%= C.CreatedOn %></CreatedOn>
<Message>
<MessageId DeliveryChannel=<%= C.DeliveryChannel %>>123456</MessageId>
<Prospect>
<Id>12345678</Id>
</Prospect>
<SentDate>2014-06-24T12:00:01</SentDate>
<CName>x1</CName>
<CNameId>1234</CNameId>
</Message>
<Message>
<MessageId DeliveryChannel="2">1236457</MessageId>
<Prospect>
<Id>12345679</Id>
</Prospect>
<SentDate>2014-06-24T12:00:02</SentDate>
<CName>x2</CName>
<CNameId>1235</CNameId>
</Message>
</Program>
MyData.Save("filename here", SaveOptions.None)
Next
我决定使用XmlWriter来编写这个XML。代码:
private static SqlDataReader GetData(SqlConnection sqlConn, int subscriberID, DateTime fromDate, DateTime toDate)
{
SqlDataReader rdr;
using (System.Data.SqlClient.SqlCommand cmd = new SqlCommand("GetData", sqlConn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SubscriberId", subscriberID);
cmd.Parameters.AddWithValue("@FromDate", fromDate);
cmd.Parameters.AddWithValue("@ToDate", toDate);
rdr = cmd.ExecuteReader();
}
return (rdr);
}
private static string WriteXml(SqlConnection sqlConn)
{
string xmlOutput = "";
System.Data.SqlClient.SqlDataReader rdrDealers = GetDealers(sqlConn);
{
while (rdrDealers.Read())
{
using (SqlDataReader rdrCalls = GetData(...)
{
if (rdrCalls.HasRows)
{
XmlWriterSettings wSettings = new XmlWriterSettings();
wSettings.Indent = true;
MemoryStream ms = new MemoryStream();
XmlWriter xw = XmlWriter.Create(ms, wSettings);
xw.WriteStartDocument();
xw.WriteStartElement("program");
xw.WriteAttributeString("xmlns", "xsi", null, "http://www.w3.org/2001/XMLSchema-instance");
xw.WriteAttributeString("xsi", "noNamespaceSchemaLocation", null, "program.xsd");
{
string CContractId = Convert.ToString(rdrDealers.GetInt32(1));
string clientName = rdrDealers.GetString(2);
xw.WriteStartElement("Customer");
xw.WriteStartElement("CContractId");
xw.WriteString(CContractId);
xw.WriteEndElement();
xw.WriteStartElement("ClientName");
xw.WriteString(clientName);
xw.WriteEndElement();
xw.WriteStartElement("SubscriberId");
xw.WriteString(Convert.ToString(subscriberID));
xw.WriteEndElement();
xw.WriteEndElement();
xw.WriteStartElement("CreatedOn");
xw.WriteString(DateTime.UtcNow.ToString("o"));
xw.WriteEndElement();
while (rdrCalls.Read())
{
string messageID = Convert.ToString(rdrCalls[0] as int?); // rdrCalls[0] as string;
string deliveryChannelID = Convert.ToString(rdrCalls[1] as int?); //Convert.ToString(rdrCalls.GetInt32(1));
string sentTms = (rdrCalls[2] as DateTime? ?? default(DateTime)).ToUniversalTime().ToString("o");
string campaignName = rdrCalls[3] as string;
string prospectID = rdrCalls[4] as string;
string myCampaignID = rdrCalls[5] as string;
xw.WriteStartElement("Message");
xw.WriteStartElement("MessageId");
xw.WriteAttributeString("DeliveryChannel", Convert.ToString(deliveryChannelID));
xw.WriteString(messageID);
xw.WriteEndElement();
xw.WriteStartElement("Prospect");
xw.WriteStartElement("Id");
xw.WriteString(prospectID);
xw.WriteEndElement();
xw.WriteEndElement();
xw.WriteStartElement("SentTms");
xw.WriteString(sentTms);
xw.WriteEndElement();
xw.WriteStartElement("CampaignName");
xw.WriteString(campaignName);
xw.WriteEndElement();
xw.WriteStartElement("myCampaignId");
xw.WriteString(myCampaignID);
xw.WriteEndElement();
xw.WriteEndElement();
}
}
xw.WriteEndDocument();
xw.Flush();
Byte[] buffer = new Byte[ms.Length];
buffer = ms.ToArray();
xmlOutput = System.Text.Encoding.UTF8.GetString(buffer);
string filePath = ...
File.WriteAllText(filePath, xmlOutput);
using (WebClient client = new WebClient())
{
client.UploadFile(url, filePath);
}
}
}
}
}
return xmlOutput;
}