sql server FOR XML xpath模式:如何生成嵌套的XML id 1和2?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (158)

如下所示。我想使用SQLserver来生成这个XML字符串。我很难得到id=“ip 1”和id=“ip 2”。你能帮我一下吗?非常感谢

    <root>
    <InsuredOrPrincipal id="IP1">
    <GeneralPartyInfo>
    <NameInfo>
    <PersonName>
    <Surname>A </Surname>
    <GivenName>B</GivenName>
    </PersonName>

    </NameInfo>

    </GeneralPartyInfo>

    </InsuredOrPrincipal>


    <InsuredOrPrincipal id="IP2">
    <GeneralPartyInfo>
    <NameInfo>
    <PersonName>
    <Surname>A </Surname>
    <GivenName>B</GivenName>
    </PersonName>

    </NameInfo>

    </GeneralPartyInfo>

    </InsuredOrPrincipal>
    </root>
提问于
用户回答回答于

可以在SSMS中运行代码来查看它的运行情况。

-- create table variable
DECLARE @table TABLE ( id VARCHAR(10), Surname VARCHAR(50), GivenName VARCHAR(50) );

-- insert test data
INSERT INTO @table ( 
    id, Surname, GivenName 
)
VALUES
( 'IP1', 'A1', 'B1' )
, ( 'IP2', 'A2', 'B2' );

-- return xml results from test data as per required schema
SELECT
    id AS 'InsuredAsPrincipal/@id'
    , Surname AS 'InsuredAsPrincipal/GeneralPartyInfo/NameInfo/PersonName/Surname'
    , GivenName AS 'InsuredAsPrincipal/GeneralPartyInfo/NameInfo/PersonName/GivenName'
FROM @table
FOR XML PATH ( '' ), ROOT( 'root' );

返回的结果XML是:

<root>
  <InsuredAsPrincipal id="IP1">
    <GeneralPartyInfo>
      <NameInfo>
        <PersonName>
          <Surname>A1</Surname>
          <GivenName>B1</GivenName>
        </PersonName>
      </NameInfo>
    </GeneralPartyInfo>
  </InsuredAsPrincipal>
  <InsuredAsPrincipal id="IP2">
    <GeneralPartyInfo>
      <NameInfo>
        <PersonName>
          <Surname>A2</Surname>
          <GivenName>B2</GivenName>
        </PersonName>
      </NameInfo>
    </GeneralPartyInfo>
  </InsuredAsPrincipal>
</root>

扫码关注云+社区

领取腾讯云代金券