TSQL XML 函数 [英] TSQL XML FUNCTION

查看:55
本文介绍了TSQL XML 函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要以下方面的帮助,因为我对 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆