我正在尝试读取一个XML,除了名为的属性外,它总体上运行得很好
<custom-attribute attribute-id="loyaltyNumber">1234567890</custom-attribute>
这是我试图得到的值";1234567890";
这是测试代码:
DECLARE @XML XML = '<customers>
<customer customer-no="00000001">
<credentials>
<login>test@email.com</login>
</credentials>
<profile>
<preferred-locale>fr_BE</preferred-locale>
<custom-attributes>
<custom-attribute attribute-id="ServiceId">1</custom-attribute>
<custom-attribute attribute-id="loyaltyNumber">1234567890</custom-attribute>
</custom-attributes>
</profile>
<note/>
</customer>
<customer customer-no="00000002">
<credentials>
<login>test2@email.com</login>
</credentials>
<profile>
<preferred-locale>fr_FR</preferred-locale>
<custom-attributes>
<custom-attribute attribute-id="loyaltyNumber">1234567890</custom-attribute>
</custom-attributes>
</profile>
<note/>
</customer>
</customers>'
SELECT
CustomerNo = Events.value('@customer-no', 'int'),
--EventType = Events.value('@Type', 'varchar(20)'),
CustomerLogin =Events.value('(credentials/login)[1]', 'varchar(60)'),
CustomerLocal =Events.value('(profile/preferred-locale)[1]', 'varchar(60)'),
EventKind =Events.value('(profile/custom-attributes/custom-attribute)[2]', 'varchar(60)')
FROM
@XML.nodes('/customers/customer') AS XTbl(Events)
目前的结果是:
客户编号 | 客户登录 | 客户本地>EventKind |
---|---|---|
1 | test@email.com | fr_BE | <123467890>
2 | test2@email.com | fr_fr | 空//tr>
。。过滤具有属性id="忠诚号码"的属性的路径
EventKind =Events.value('(profile/custom-attributes/custom-attribute[@attribute-id="loyaltyNumber"])[1]', 'varchar(60)')
您只需将自定义属性的属性id添加到查询中即可:
DECLARE @XML XML = '<customers>
<customer customer-no="00000001">
<credentials>
<login>test@email.com</login>
</credentials>
<profile>
<preferred-locale>fr_BE</preferred-locale>
<custom-attributes>
<custom-attribute attribute-id="ServiceId">1</custom-attribute>
<custom-attribute attribute-id="loyaltyNumber">1234567890</custom-attribute>
</custom-attributes>
</profile>
<note/>
</customer>
<customer customer-no="00000002">
<credentials>
<login>test2@email.com</login>
</credentials>
<profile>
<preferred-locale>fr_FR</preferred-locale>
<custom-attributes>
<custom-attribute attribute-id="loyaltyNumber">1234567777</custom-attribute>
</custom-attributes>
</profile>
<note/>
</customer>
</customers>'
SELECT
CustomerNo = Events.value('@customer-no', 'int'),
--EventType = Events.value('@Type', 'varchar(20)'),
CustomerLogin =Events.value('(credentials/login)[1]', 'varchar(60)'),
CustomerLocal =Events.value('(profile/preferred-locale)[1]', 'varchar(60)'),
EventKind =Events.value('(profile/custom-attributes/custom-attribute[@attribute-id = "loyaltyNumber"])[1]', 'varchar(60)')
FROM
@XML.nodes('/customers/customer') AS XTbl(Events)