代码之家 › 专栏 › 技术社区 › TonyP

如何将sql:column()插入xml

sqlxml tsql sql-server-2008

0

TonyP · 技术社区 · 7 年前

    <items>
  <item>
    <position>2</position>
    <code>123-122</code>
    <description>Circulator 5GPM</description>
  </item>
  <item>
    <position>4</position>
    <code>124-128</code>
    <description>Circulator 25GPM</description>
  </item>
</items>

我想从关系表中插入值,如

最好使用T-Sql Sql:column(“position”)等。。

1 回复 | 直到 7 年前

1

TriV 7 年前

你可以 insert 像这样将新节点转换为xml

DECLARE @xml XML = '<items>
  <item>
    <position>2</position>
    <code>123-122</code>
    <description>Circulator 5GPM</description>
  </item>
  <item>
    <position>4</position>
    <code>124-128</code>
    <description>Circulator 25GPM</description>
  </item>
</items>'

DECLARE @SampleData AS TABLE
(
    position int,
    code varchar(20),
    [description] varchar(100)
)
INSERT INTO @SampleData
VALUES 
(1,'123-123','description 1'),
(2,'124-124','description 2')


DECLARE @NewXmlNode XML =
(
    SELECT * 
    FROM @SampleData
    FOR XML PATH('item'),TYPE
)
SELECT @xml, @NewXmlNode

SET @xml.modify('
    insert sql:variable("@NewXmlNode") as last into (/items)[1]
')

结果:

<items>
  <item>
    <position>2</position>
    <code>123-122</code>
    <description>Circulator 5GPM</description>
  </item>
  <item>
    <position>4</position>
    <code>124-128</code>
    <description>Circulator 25GPM</description>
  </item>
  <item>
    <position>1</position>
    <code>123-123</code>
    <description>description 1</description>
  </item>
  <item>
    <position>2</position>
    <code>124-124</code>
    <description>description 2</description>
  </item>
</items>