XML Xquery如何在特定字符之前删除字符串?

Question

我正在尝试替换xquery中元素的部分数据。

在我的数据库(Sql Server 2017)中，我有一个包含一千个XML的表。

请找到下面我的XML的一个例子

<Policies>
<Policy>
<GroupUserName>WERDAA12BB34</GroupUserName>
<GroupUserId>AQUAAAAAAAUVAAAAMAdSpoMScJXq9mQyj30AAA==</GroupUserId>
<Roles>
<Role>
<Name>Role 1</Name>
<Description>Enter description.</Description>
</Role>
<Role>
<Name>Role 2</Name>
<Description>Enter description.</Description>
</Role>
<Role>
<Name>Role 3</Name>
<Description>Enter description.</Description>
</Role>
<Role>
<Name>Role 4</Name>
<Description>Enter description
</Role>
<Role>
<Name>Role 5</Name>
<Description>Enter description.</Description>
</Role>
</Roles>
</Policy>
<Policy>
<GroupUserName>NTKD0BB159FFN</GroupUserName>
<GroupUserId>AQUAAAAAAAUVAAAALZBmswQnK326hQEyvt0JAA==</GroupUserId>
<Roles>
<Role>
<Name>Role 1</Name>
<Description>Enter description.</Description>
</Role>
</Roles>
</Policy>
</Policies>

让我们关注这一部分我需要删除这个"之前的所有字符;">

<GroupUserName>WERDAA12BB34</GroupUserName>
<GroupUserName>NTKD0BB159FFN</GroupUserName>

预计

<GroupUserName>AA12BB34</GroupUserName>
<GroupUserName>BB159FFN</GroupUserName>

到目前为止我已经完成了:

SELECT XmlDescription,
replace(CAST(XmlDescription AS VARCHAR(8000)),'<GroupUserName>WERD','<GroupUserName>')
from xml_temp 
WHERE ID = 1;

我也试过了:

replace(CAST(XmlDescription AS VARCHAR(8000)), '^.*', '')

但是什么也没发生....

在sql中:

REPLACE(SUBSTRING(myField, CHARINDEX('', myField), LEN(myField)), '', '')

我试图实现这个方法与xquery

你能帮我吗?

问候,

create table dbo.xml_temp (
ID [uniqueidentifier] NOT NULL,
XmlDescription xml not null
);
insert into dbo.xml_temp (ID,XmlDescription) values (NEWID(),'<Policies>
<Policy>
<GroupUserName>WERDAA12BB34</GroupUserName>
<GroupUserId>AQUAAAAAAAUVAAAAMAdSpoMScJXq9mQyj30AAA==</GroupUserId>
<Roles>
<Role>
<Name>Role 1</Name>
<Description>Enter description.</Description>
</Role>         
</Roles>
</Policy>
<Policy>
<GroupUserName>NTKD0BB159FFN</GroupUserName>
<GroupUserId>AQUAAAAAAAUVAAAALZBmswQnK326hQEyvt0JAA==</GroupUserId>
<Roles>
<Role>
<Name>Role 1</Name>
<Description>Enter description.</Description>
</Role>
</Roles>
</Policy>
</Policies>')

SELECT @@version

Microsoft SQL Server 2017 (RTM)

Answer 1

请尝试以下解决方案。

这是一个解决方案，因为SQL Server XQuery甚至不完全支持XQuery v.1.0

在本例中缺少substring-after()函数。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<Policies>
<Policy>
<GroupUserName>WERDAA12BB34</GroupUserName>
<GroupUserId>AQUAAAAAAAUVAAAAMAdSpoMScJXq9mQyj30AAA==</GroupUserId>
<Roles>
<Role>
<Name>Role 1</Name>
<Description>Enter description.</Description>
</Role>
<Role>
<Name>Role 2</Name>
<Description>Enter description.</Description>
</Role>
</Roles>
</Policy>
<Policy>
<GroupUserName>NTKD0BB159FFN</GroupUserName>
<GroupUserId>AQUAAAAAAAUVAAAALZBmswQnK326hQEyvt0JAA==</GroupUserId>
<Roles>
<Role>
<Name>Role 1</Name>
<Description>Enter description.</Description>
</Role>
</Roles>
</Policy>
</Policies>');
-- DDL and sample data population, end
SELECT * 
, xmldata.query('<Policies>
{
for $x in /Policies/Policy
return <Policy>
<GroupUserName>{
if (contains(($x/GroupUserName/text())[1],"WERD")) then substring(($x/GroupUserName/text())[1],6,100)
else substring(($x/GroupUserName/text())[1],7,100)
}
</GroupUserName>
{$x/*[local-name()!="GroupUserName"]}
</Policy>
}
</Policies>') AS Result
FROM @tbl;