我在SQL Server中的存储过程
CREATE PROCEDURE ParseXML(@InputXML XML)
输入参数的数据类型是XML。
在LINQ到SQL生成code存储过程的输入参数System.Xml.Linq.XElement
[全球:: System.Data.Linq.Mapping.FunctionAttribute(NAME =dbo.ParseXML)
公共ISingleResult< ParseXMLResult> ParseXML([全球:: System.Data.Linq.Mapping.ParameterAttribute(NAME =InputXML的DbType =XML)System.Xml.Linq.XElement inputXML)
现在,我怎么可以通过以下列表中ParseXML方法,使存储过程的工作?
修改
看完答案后 - 另一种解决方案是如下
的XElement根=新的XElement(ArrayOfBankAccountDTOForStatus
新XAttribute(XNamespace.Xmlns +XSI,http://www.w3.org/2001/XMLSchema-instance),
新XAttribute(XNamespace.Xmlns +XSD,http://www.w3.org/2001/XMLSchema));
的foreach(在bankAccountDTOList VAR元)
{
的XElement EX =新的XElement(BankAccountDTOForStatus
新的XElement(BankAccountID,element.BankAccountID)
新的XElement(状态,element.Status));
root.Add(前);
}
code在提问
字符串的ConnectionString =数据源=;初始目录= LibraryReservationSystem;集成安全性= TRUE;连接超时= 30;
VAR theDataContext =新DBML_Project.MyDataClassesDataContext(的ConnectionString);
名单< DTOLayer.BankAccountDTOForStatus> bankAccountDTOList =新的名单,其中,DTOLayer.BankAccountDTOForStatus>();
DTOLayer.BankAccountDTOForStatus presentAccount1 =新DTOLayer.BankAccountDTOForStatus();
presentAccount1.BankAccountID = 5;
presentAccount1.Status =FrozenF13;
DTOLayer.BankAccountDTOForStatus presentAccount2 =新DTOLayer.BankAccountDTOForStatus();
presentAccount2.BankAccountID = 6;
presentAccount2.Status =FrozenF23;
bankAccountDTOList.Add(presentAccount1);
bankAccountDTOList.Add(presentAccount2);
//theDataContext.ParseXML(inputXML);
所需的XML结构
注:此XML用于一些操作,而不是直接存储在数据库中的XML。我需要写一个选择查询,将在XML列中的数据。
存储过程的逻辑
定义@MyTable表(RowNumber INT,BankAccountID INT,StatusVal VARCHAR(MAX))
INSERT INTO @MyTable(RowNumber,BankAccountID,StatusVal)
选择ROW_NUMBER()OVER(ORDER BY c.value('BankAccountID [1]','廉政')ASC)为行,
c.value('BankAccountID [1]','诠释'),
c.value('状态[1]','为varchar(32))
从
@ inputXML.nodes('// BankAccountDTOForStatus')T(C);
阅读
How序列化和使用LINQ to SQL
的对象数据库保存为XMLHow使用LINQ查询得到的XElement值时XElements具有相同的名称
Linq-to-SQL随着XML数据库字段 - 为什么这项工作
?http://www.sqlteam.com/forums/topic的.asp?TOPIC_ID = 176385
解决方案您可以将您的列表变成一个像这样的XML片段:
的IEnumerable<的XElement> EL = list.Select(I =>
新的XElement(BankAccountDTOForStatus
新的XElement(BankAccountID,i.BankAccountID)
新的XElement(状态,i.Status)
));
然后你可以把它变成一个的XElement:
的XElement根=新的XElement(根,EL);
现在你可以将它传递给ParseXML为参数inputXML这类型的XElement的。 在存储过程处理这件事是这样的:
定义@InputXML NVARCHAR(1024)= N'
<根>
< BankAccountDTOForStatus>
&其中; BankAccountID→2&其中; / BankAccountID>
<状态> FrozenFA< /状态>
< / BankAccountDTOForStatus>
< BankAccountDTOForStatus>
&其中; BankAccountID→3&所述; / BankAccountID>
<状态> FrozenSB< /状态>
< / BankAccountDTOForStatus>
< /根>
DECLARE @Handle INT
EXEC sp_xml_ preparedocument @Handle输出,@InputXML
选择 *
FROM OPENXML(@Handle,'/根/ BankAccountDTOForStatus',1)
WITH(
BankAccountID INT'BankAccountID /文(),
状态VARCHAR(128)状态/文()
)
EXEC sp_xml_removedocument @Handle
I have a stored procedure in SQL Server
CREATE PROCEDURE ParseXML (@InputXML xml)
The data type for input parameter is "xml".
In the LINQ to SQL generated code for the stored procedure the input parameter is System.Xml.Linq.XElement
[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.ParseXML")]
public ISingleResult<ParseXMLResult> ParseXML([global::System.Data.Linq.Mapping.ParameterAttribute(Name="InputXML", DbType="Xml")] System.Xml.Linq.XElement inputXML)
Now, how can I pass the following List to the ParseXML method to make the stored procedure work?
EDIT:
After reading the answer - another solution is listed below
XElement root = new XElement("ArrayOfBankAccountDTOForStatus",
new XAttribute(XNamespace.Xmlns + "xsi", "http://www.w3.org/2001/XMLSchema-instance"),
new XAttribute(XNamespace.Xmlns + "xsd", "http://www.w3.org/2001/XMLSchema"));
foreach (var element in bankAccountDTOList)
{
XElement ex= new XElement("BankAccountDTOForStatus",
new XElement("BankAccountID", element.BankAccountID),
new XElement("Status", element.Status));
root.Add(ex);
}
CODE In Question
string connectionstring = "Data Source=.;Initial Catalog=LibraryReservationSystem;Integrated Security=True;Connect Timeout=30";
var theDataContext = new DBML_Project.MyDataClassesDataContext(connectionstring);
List<DTOLayer.BankAccountDTOForStatus> bankAccountDTOList = new List<DTOLayer.BankAccountDTOForStatus>();
DTOLayer.BankAccountDTOForStatus presentAccount1 = new DTOLayer.BankAccountDTOForStatus();
presentAccount1.BankAccountID = 5;
presentAccount1.Status = "FrozenF13";
DTOLayer.BankAccountDTOForStatus presentAccount2 = new DTOLayer.BankAccountDTOForStatus();
presentAccount2.BankAccountID = 6;
presentAccount2.Status = "FrozenF23";
bankAccountDTOList.Add(presentAccount1);
bankAccountDTOList.Add(presentAccount2);
//theDataContext.ParseXML(inputXML);
Required XML Structure
Note: This XML is used for some operations, not for directly storing in database as XML. I need to write a select query that will list the data from the XML.
Stored Procedure Logic
DECLARE @MyTable TABLE (RowNumber int, BankAccountID int, StatusVal varchar(max))
INSERT INTO @MyTable(RowNumber, BankAccountID,StatusVal)
SELECT ROW_NUMBER() OVER(ORDER BY c.value('BankAccountID[1]','int') ASC) AS Row,
c.value('BankAccountID[1]','int'),
c.value('Status[1]','varchar(32)')
FROM
@inputXML.nodes('//BankAccountDTOForStatus') T(c);
READING
How to serialize and save an object to database as Xml using Linq to SQL
How to use a LINQ query to get XElement values when XElements have same name
Linq-to-SQL With XML Database Fields -- Why does this work?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176385
解决方案
You can turn your list into an XML fragment like this:
IEnumerable<XElement> el = list.Select(i =>
new XElement("BankAccountDTOForStatus",
new XElement("BankAccountID", i.BankAccountID),
new XElement("Status", i.Status)
));
Then you can turn it into an XElement:
XElement root = new XElement("root", el);
Now you can just pass it to ParseXML as parameter inputXML which is of type XElement. In stored procedure handle it like this:
DECLARE @InputXML NVARCHAR(1024) = N'
<root>
<BankAccountDTOForStatus>
<BankAccountID>2</BankAccountID>
<Status>FrozenFA</Status>
</BankAccountDTOForStatus>
<BankAccountDTOForStatus>
<BankAccountID>3</BankAccountID>
<Status>FrozenSB</Status>
</BankAccountDTOForStatus>
</root>'
DECLARE @handle INT
EXEC sp_xml_preparedocument @handle OUTPUT, @InputXML
SELECT *
FROM OPENXML(@handle, '/root/BankAccountDTOForStatus', 1)
WITH (
BankAccountID INT 'BankAccountID/text()',
Status VARCHAR(128) 'Status/text()'
)
EXEC sp_xml_removedocument @handle
相关推荐
最新文章