如何创建一个元素及其值将在xml中的其他元素值上计算？ [英] how to create an element and its value will be compute on other element value in xml?

问题描述

大家好。我正在处理XML。我想创建一个元素，它的值将被计算为其他元素值的总和，如下所示

Hello all.I am working on XML.I want to create an element and its value will be computed as the sum of the other elements values like as follows

<Name>
  <firstName>Christophe</firstName>
  <middleName>Robert</middleName>
  <lastName>Mulford</lastName>
  <maidenName/>
  <suffix>SR</suffix>
  <autoLastName>Mulford SR</autoLastName>
  <autoFullName>Christophe Robert Mulford SR</autoFullName>
</Name>

在上面的代码中，元素名称是XML复杂数据类型

in the above code the element Name is of XML complex data type

<xs:complexType name="cdms_DetailedName">
       <xs:sequence>
           <xs:element name="firstName" type="xs:string"/>
           <xs:element name="middleName" type="xs:string"/>
           <xs:element name="lastName" type="xs:string"> </xs:element>
           <xs:element name="maidenName" type="xs:string"/>
           <xs:element name="suffix">
               <xs:simpleType>
                   <xs:restriction base="xs:string">
                       <xs:enumeration value="JR"/>
                       <xs:enumeration value="SR"/>
                       <xs:enumeration value="II"/>
                       <xs:enumeration value="III"/>
                       <xs:enumeration value="IV"/>
                       <xs:enumeration value="V"/>
                   </xs:restriction>
               </xs:simpleType>
           </xs:element>
           <xs:element name="autoLastName"/>
           <xs:element name="autoFullName"/>
       </xs:sequence>
   </xs:complexType>

和 autoFullName 是 firstName +'' ''+ middleName +'' ''+ lastName +'' ''+ suffix

的结果



我想通过一次在XML模式文件或XML数据类型中指定公式来自动执行此操作，以便 autoFullName 元素将自动计算。

and the autoFullName is the result of firstName+'' ''+middleName+'' ''+lastName+'' ''+suffix


I want to do this automatically by specifying a formula in XML schema file or in the XML data type at once so that the autoFullName element will be computed automatically.

推荐答案

创建此程序

Create this procedure

CREATE PROCEDURE PopulateFullNameInThisXML
	@XmlData XML
AS
BEGIN

DECLARE @xmltble TABLE
(
        firstName NVARCHAR(MAX),
        middleName NVARCHAR(MAX),
        lastName NVARCHAR(MAX),
        maidenName NVARCHAR(MAX),
        suffix NVARCHAR(MAX),
        autoLastName NVARCHAR(MAX),
        autoFullName NVARCHAR(MAX)
)


INSERT INTO @xmltble
select 
R.i.query('firstName').value('.', 'varchar(30)') [firstName],
R.i.query('middleName').value('.', 'varchar(30)') [middleName],
R.i.query('lastName').value('.', 'varchar(30)') [lastName],
R.i.query('maidenName').value('.', 'varchar(30)') [maidenName],
R.i.query('suffix').value('.', 'varchar(30)') [suffix],
R.i.query('autoLastName').value('.', 'varchar(30)') [autoLastName],
R.i.query('autoFullName').value('.', 'varchar(30)') [autoFullName]
from @XmlData.nodes('/Name') R(i)


SELECT 
 [firstName],
 [middleName],
 [lastName],
 [maidenName],
 [suffix],
 lastName + suffix AS [autoLastName],
 [firstName] + [middleName]  +  lastName + suffix AS [autoFullName]
FROM @xmltble FOR XML PATH('Name')


END

现在使用它如下

Now use it as below

DECLARE @XmlData xml


SET @XmlData='    <name>
        <firstname>Christophe</firstname>
        <middlename>Robert</middlename>
        <lastname>Mulford</lastname>
        <maidenname />
        <suffix>SR</suffix>
        <autolastname>Mulford SR</autolastname>
        <autofullname></autofullname>
      </name>
<name>
        <firstname>Christophe</firstname>
        <middlename>Robert</middlename>
        <lastname>Mulford</lastname>
        <maidenname />
        <suffix>SR</suffix>
        <autolastname>Mulford SR</autolastname>
        <autofullname></autofullname>
      </name>'


exec PopulateFullNameInThisXML @XmlData

你会得到这样的输出

You will get output like this

<Name>
  <firstName>Christophe</firstName>
  <middleName>Robert</middleName>
  <lastName>Mulford</lastName>
  <maidenName></maidenName>
  <suffix>SR</suffix>
  <autoLastName>MulfordSR</autoLastName>
  <autoFullName>ChristopheRobertMulfordSR</autoFullName>
</Name>
<Name>
  <firstName>Christophe</firstName>
  <middleName>Robert</middleName>
  <lastName>Mulford</lastName>
  <maidenName></maidenName>
  <suffix>SR</suffix>
  <autoLastName>MulfordSR</autoLastName>
  <autoFullName>ChristopheRobertMulfordSR</autoFullName>
</Name>

以防万一RD'的解决方案（NO 1）给你适合（正如我在按原样尝试时那样），一个稍微重做的副本做了为我工作。

Just in case RD''s solution (NO 1) is giving you fits (as it did me when I tried it "as-is"), a slightly reworked copy which did work for me.

CREATE PROCEDURE sp_SW_PopulateFullNameInThisXML
	@XmlData XML
	AS
	BEGIN
	
		DECLARE @xmltble TABLE
		(
				[firstName] NVARCHAR(MAX),
				[middleName] NVARCHAR(MAX),
				[lastName] NVARCHAR(MAX),
				[maidenName] NVARCHAR(MAX),
				[suffix] NVARCHAR(MAX),
				[autoLastName] NVARCHAR(MAX),
				[autoFullName] NVARCHAR(MAX)
		)
		INSERT INTO @xmltble
			SELECT
				R.i.value(''firstName[1]'', ''varchar(30)'') AS [firstName],
				R.i.value(''middleName[1]'', ''varchar(30)'') AS [middleName],
				R.i.value(''lastName[1]'', ''varchar(30)'') AS [lastName],
				R.i.value(''maidenName[1]'', ''varchar(30)'') AS [maidenName],
				R.i.value(''suffix[1]'', ''varchar(30)'') AS [suffix],
				R.i.value(''autoLastName[1]'', ''varchar(30)'') AS [autoLastName],
				R.i.value(''autoFullName[1]'', ''varchar(30)'') AS [autoFullName]
					FROM @XmlData.nodes(''/name'') AS R(i)
		 
		SELECT 
			 [firstName],
				[middleName],
					[lastName],
						[maidenName],
							[suffix],
								[lastName] + [suffix] AS [autoLastName],
									[firstName] + [middleName] + [lastName] + [suffix] AS [autoFullName]
				FROM @xmltble FOR XML PATH(''Name'')
	END 		

运行SP

Run the SP

DECLARE @XmlData xml
SET @XmlData   =         ''<name>
				<firstname>Christophe</firstname>
				<middlename>Robert</middlename>
				<lastname>Mulford</lastname>
				<maidenname></maidenname>
				<suffix>SR</suffix>
				<autolastname>Mulford SR</autolastname>
				<autofullname></autofullname>
			  </name>
			  <name>
				<firstname>Christophe</firstname>
				<middlename>Robert</middlename>
				<lastname>Mulford</lastname>
				<maidenname></maidenname>
				<suffix>SR</suffix>
				<autolastname>Mulford SR</autolastname>
				<autofullname></autofullname>
			  </name>''

EXEC sp_SW_PopulateFullNameInThisXML @XmlData		

感谢您的帮助RD ..我真的很挣扎SW的确切问题！

Thanks for the help RD .. I really was struggling with SW''s exact problem!

这篇关于如何创建一个元素及其值将在xml中的其他元素值上计算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！