希望有人能帮助我进行SQL查询,我正在try 合并两个XML列,我目前使用的是SQL Azure 2019.
第一个XML
<HOME>
<VALIDITYLIST>
<VALIDITY STATE="1">
<VALIDITYTYPE>1</VALIDITYTYPE>
<GROUPCODE>DEFAULT</GROUPCODE>
<ENTRY/>
<CARD>2</CAR>
<GIFTAID/>
<VARIABLERANGE>false</VARIABLERANGE>
<DAYS>365</DAYS>
<NOTOPERATING>false</NOTOPERATING>
<VALIDITYLIST/>
<YPERESTRICTIONLIST/>
<METRALOCKERV2>
<LOCKERITEMID/>
</METRALOCKERV2>
<REQUIREDVAREXPDATE/>
</VALIDITY>
</VALIDITYLIST>
</HOME>
第二个XML
<HOME>
<VALIDITYLIST>
<VALIDITY STATE="1">
<VALIDITYTYPE>1</VALIDITYTYPE>
<GROUPCODE>DEFAULT</GROUPCODE>
<GIFTAID/>
<DYNAMICP/>
<VALIDITYLIST>
<VALIDITY STATE="1">
<VALIDITYTYPE>2</VALIDITYTYPE>
<EVENT>3</EVENT>
<ENTRYTYPE>2</ENTRYTYPE>
<NUMENTRY>1</NUMENTRY>
</VALIDITY>
</VALIDITYLIST>
</VALIDITY>
</VALIDITYLIST>
</HOME>
SQL以某种方式合并了两个XML,并且只返回值所在的 node .就像这样.
<HOME>
<VALIDITYLIST>
<VALIDITY>
<VALIDITYTYPE>1</VALIDITYTYPE>
<GROUPCODE>DEFAULT</GROUPCODE>
<CARD>2</CAR>
<VARIABLERANGE>false</VARIABLERANGE>
<DAYS>365</DAYS>
<NOTOPERATING>false</NOTOPERATING>
<VALIDITYLIST>
<VALIDITY>
<VALIDITYTYPE>2</VALIDITYTYPE>
<EVENT>3</EVENT>
<ENTRYTYPE>2</ENTRYTYPE>
<NUMENTRY>1</NUMENTRY>
</VALIDITY>
</VALIDITYLIST>
</VALIDITY>
</VALIDITYLIST>
</HOME>
感谢大家分享你的 idea [编辑部分如下]
我仍然希望在SQL中处理.由于 node 是固定的,我正在考虑通过读取单独的XML创建两个表,然后根据值构造新的XML,现在我停留在第一部分,即读取XML. 这就是我想要的,当我读取第二个XML但返回空值时,我应该会看到值1和2,你能看到我做错了什么吗?
select @XML2.value('(/HOME/VALIDITYTYPE/node())[1]', 'nvarchar(max)') as VALIDITYTYPE
, @XML2.value('(/HOME/VALIDITYLIST/VALIDITYLIST/VALIDITYTYPE/node())[1]', 'nvarchar(max)') as VALIDITYTYPE