所以我有了这个XML:

<qrcode xmlns="http://vital.nl/qrcodeSchema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Echeq>
    <echeqFamilyId>67c8590e-66fe-43e1-9124-a2163a6365df</echeqFamilyId>
  </Echeq>
</qrcode>

但我想要得到这个:

<qrcode xmlns="http://vital.nl/qrcodeSchema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Echeq>
 <awardedVPoints>0</awardedVPoints>
    <echeqFamilyId>67c8590e-66fe-43e1-9124-a2163a6365df</echeqFamilyId>
  </Echeq>
</qrcode>

所以我试着这样做:

      UPDATE[dbo].[QRCodeDefinitions]   
   SET[QRCodeXml] = CAST(REPLACE(
   CAST([QRCodeXml] as nvarchar(max)), '',  '<awardedVPoints>0') 
   as xml) from [dbo].[QRCodeDefinitions] 
   where qrcode = 'newvcheq'

但XML中没有任何变化。

那我要改变什么呢？

谢谢

好吧，所以我试着这样做:

    ;WITH XMLNAMESPACES(N'http://vital10.nl/qrcodeSchema.xsd' AS ns)
   UPDATE [dbo].[QRCodeDefinitions]
SET QRCodeXml = QRCodeXml.query('
declare default element namespace "http://vital.nl/qrcodeSchema.xsd";
<qrcode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema">
{
    for $x in ns:/qrcode/*
    return (
        if (empty($x/../awardedVPoints)) then (<awardedVPoints> {"0"}</awardedVPoints>)
        else (), $x)
}
</qrcode>')
FROM [dbo].[QRCodeDefinitions]
where  qrcode = 'newvcheq'

但后来我发现:

Msg 9332, Level 16, State 1, Line 38
XQuery [dbo.QRCodeDefinitions.QRCodeXml.query()]: Syntax error near ':', expected 'where', '(stable) order by' or 'return'.

如果我这样做:

   UPDATE [dbo].[QRCodeDefinitions]
SET [QRCodeXml] = [QRCodeXml].query('
declare default element namespace "http://vital.nl/qrcodeSchema.xsd";
<qrcode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema">
{
    for $x in /qrcode/*
    return (
        if (empty($x/../awardedVPoints)) then (<awardedVPoints> {"0"}</awardedVPoints>)
        else (), $x)
}
</qrcode>')
FROM [dbo].[QRCodeDefinitions]
where  qrcode = 'newvcheq'

我得到了这个错误:所以我必须改变什么。 谢谢

XML Validation: Invalid content. Expected element(s): '{http://vital.nl/qrcodeSchema.xsd}ExternalUrl','{http://vital.nl/qrcodeSchema.xsd}Platform','{http://vital.nl/qrcodeSchema.xsd}Image','{http://vital10.nl/qrcodeSchema.xsd}Location','{http://vital.nl/qrcodeSchema.xsd}Echeq','{http://vital.nl/qrcodeSchema.xsd}Video'. Found: element '{http://vital10.nl/qrcodeSchema.xsd}awardedVPoints' instead. Location: /*:qrcode[1]/*:awardedVPoints[1].

表格设计。 这就是表格的细节。 使用所有表字段

CREATE TABLE [dbo].[QRCodeDefinitions](
    [QRCode] [nvarchar](256) NOT NULL,
    [Title] [nvarchar](256) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [CreatedBy] [uniqueidentifier] NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [UpdatedOn] [datetime2](7) NOT NULL,
    [ValidUntil] [datetime2](7) NOT NULL,
    [LastScannedOn] [datetime2](7) NULL,
    [TotalScanned] [bigint] NOT NULL,
    [MaxAllowedToScan] [bigint] NULL,
    [MaxAllowedToScanByParticipant] [bigint] NULL,
    [ScanFrequency] [bigint] NULL,
    [ScanFrequencyType] [int] NOT NULL,
    [ActionType] [int] NOT NULL,
    [QRCodeXml] [xml](CONTENT [dbo].[QRCodeSchema-2019-07-23]) NULL,
    [PublishState] [int] NOT NULL,
 CONSTRAINT [PK_QRCodeDefinitions] PRIMARY KEY CLUSTERED 
(
    [QRCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[QRCodeDefinitions] ADD  DEFAULT ((0)) FOR [PublishState]
GO