TSQL XML 函数 [英] TSQL XML FUNCTION
问题描述
我需要以下方面的帮助,因为我对 SQL 中的 XML 不了解.下面是生成表格和数据的表格脚本.
I need help with the following, as my knowledge of XML within SQL is non existent. Below is the table scripts to generate the table and data.
在 XML 中有一个名称属性为RequiredField"的 XML 元素,我需要创建一个 TSQL 函数来返回 value 属性中包含的值.请任何人都可以提供帮助.
In the XML there is a XML Element with a name attribute of "RequiredField", i need to create a TSQL function to return the value contained in the value attribute. Please can anyone assist.
CREATE TABLE [MyTable]
( [UniqueID] INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[Description] VARCHAR(50) NOT NULL,
[MetaData] XML NOT NULL )
INSERT INTO [MyTable]
( [Description], [MetaData] )
SELECT 'My Description 1', '<properties xmlns="http://schemas.myschema.com/propertyInfo/additionalPropertyInfo.xsd"><item name="PropertyName" value="Property1" /><item name="RequiredField" value="true" /></properties>'
UNION
SELECT 'My Description 2', '<properties xmlns="http://schemas.myschema.com/propertyInfo/additionalPropertyInfo.xsd"><item name="PropertyName" value="Property2" /></properties>'
如您所见,第二行不包含该元素,因此它应该返回一个空值.我似乎在查询中绕圈子,除了命名空间部分之外并没有真正取得太大进展,但即使我不确定是对的.
As you can see the second row doesnt contain that element, so it should return a null value. I seem to be running around in circles with the query, and havent really made much progress besides for the namespace part, but even that im not sure is right.
大致是这样的
CREATE FUNCTION GetRequiredFieldValue(@uniqueID INT)
RETURNS BIT
AS
...
推荐答案
试试这个:
create function GetRequiredFieldValue(@uniqueID INT) returns bit
as
begin
declare @Ret bit;
with xmlnamespaces(default 'http://schemas.myschema.com/propertyInfo/additionalPropertyInfo.xsd')
select @Ret = MetaData.value('(/properties/item[@name = "RequiredField"])[1]/@value', 'bit')
from MyTable
where UniqueID = @UniqueID;
return @Ret;
end
这篇关于TSQL XML 函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!