在SQL Server中读取动态XML节点

debugcn 发表于 Dev

标记

我具有以下XML结构：

set @MailXML =
'<MailingCompany>
    <Mailman>
        <Name>Jamie</Name>
            <Age> 24 </Age>
            <Letter>
            <DestinationAddress> 440 Mountain View Parade </DestinationAddress>
            <DestinationCountry> USA </DestinationCountry>
                <OriginCountry> Australia </OriginCountry>
            <OriginAddress> 120 St Kilda Road </OriginAddress>
            </Letter>
    </Mailman>
</MailingCompany>'

我的SQL当前看起来像这样：

-- Mail Insertion
INSERT INTO mailDB.dbo.Mailman
   SELECT
       m.value('Name[1]','varchar(50)') as Name,
       m.value('Age[1]','varchar(50)') as Age
   FROM 
       @MailXML.nodes('/MailingCompany/Mailman') as A(m)

SET @MailPersonFK = SCOPE_IDENTITY();

-- Letter Insertion
INSERT INTO mailDB.dbo.Letter
    SELECT 
        l.value('DestinationAddress[1]', 'varchar(50)') as DestinationAddress,
        l.value('DestinationCountry[1]', 'varchar(50)') as DestinationCountry,
        l.value('OriginCountry[1]', 'varchar(50)') as OriginCountry,
        l.value('OriginAddress[1]', 'varchar(50)') as OriginAddress
        @MailPersonFK as MailID
    FROM  
        @MailXML.nodes('MailingCompany/Mailman/Letter') as B(l)

我正在尝试将Mailman和信函数据提取到各自的表中。我已经开始工作了，但是我的问题是MailCompany节点是动态的。有时，它可能是MailVehicle，例如，我仍然需要阅读相应的Mailman和Letter节点的数据，并把它们插入到自己相应的表。

所以两者

FROM @MailXML.nodes('/MailingCompany/Mailman') as A(t)

和

FROM @MailXML.nodes('MailingCompany/Mailman/Letter') as B(l)

需要进行更改以使其MailingCompany具有动态性。

我尝试提取父节点并将其连接成一个字符串，以放入.nodes函数中，如下所示：

set @DynXML = '/' + @parentNodeVar + '/Mailman'

FROM @MailXML.nodes(@DynXML) as A(t)

但是我收到以下错误：

XML数据类型方法“节点”的参数1必须是字符串文字。

如何克服这个动态XML问题？

提前非常感谢你

Shnugo

看这个简化的例子：

DECLARE @xml1 XML=
N'<MailingCompany>
  <Mailman>
    <Name>Jamie</Name>
    <Letter>
      <DestinationAddress> 440 Mountain View Parade </DestinationAddress>
    </Letter>
  </Mailman>
</MailingCompany>';

DECLARE @xml2 XML=
N'<OtherName>
  <Mailman>
    <Name>Jodie</Name>
    <Letter>
      <DestinationAddress> This is the other address </DestinationAddress>
    </Letter>
  </Mailman>
</OtherName>';

SELECT @xml1.value(N'(*/Mailman/Name)[1]','nvarchar(max)') AS Mailman_Name
      ,@xml1.value(N'(*/Mailman/Letter/DestinationAddress)[1]','nvarchar(max)') AS DestinationAddress
SELECT @xml2.value(N'(*/Mailman/Name)[1]','nvarchar(max)') AS Mailman_Name
      ,@xml2.value(N'(*/Mailman/Letter/DestinationAddress)[1]','nvarchar(max)') AS DestinationAddress

您可以用替换节点的名称*。

另一个技巧是使用（与之前相同的结果）进行深度搜索//：

SELECT @xml1.value(N'(//Name)[1]','nvarchar(max)') AS Mailman_Name
      ,@xml1.value(N'(//DestinationAddress)[1]','nvarchar(max)') AS DestinationAddress
SELECT @xml2.value(N'(//Name)[1]','nvarchar(max)') AS Mailman_Name
      ,@xml2.value(N'(//DestinationAddress)[1]','nvarchar(max)') AS DestinationAddress

一般规则：尽可能具体。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。