由于性能问题,SQL Cross的替代应用
我陷入了一点辙。我有一个XML和SQl服务器。我想让所有FBC的值用逗号分隔。由于性能问题,SQL Cross的替代应用
表的名字是Table1。存储xml的列的名称是FareDetails。 BookingID和ID是表1的其他列。
SELECT
(PTSD.PSTDNode.value('(FBC)[1]', 'VARCHAR(1024)') + ',') [text()]
FROM
[Table1]
CROSS APPLY [FareDetails].nodes('/AirFareInfo/PTSDPFS/PTSD') PTSD(PSTDNode)
WHERE
[BookingID] = 123
ORDER BY
[AirTraveler].ID ASC
FOR XML PATH ('')
但是我在数据库中有百万条记录,这对性能造成了巨大的损失。
的XML:
<AirFareInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IPFA="false">
<PT>Flight</PT>
<FPMID>0</FPMID>
<PTID>1</PTID>
<FS>
<CID>2</CID>
<Value>0</Value>
</FS>
<TF>
<CID xsi:nil="true" />
<Value>0</Value>
</TF>
<VF>
<CID>2</CID>
<Value>0</Value>
</VF>
<VD>
<CID>2</CID>
<Value>0</Value>
</VD>
<VCR xsi:nil="true" />
<VC>
<CID>2</CID>
<Value>0</Value>
</VC>
<VFC>
<CID>2</CID>
<Value>0</Value>
</VFC>
<VST />
<VIT />
<AAPFVDR xsi:nil="true" />
<CC>
<CID>2</CID>
<Value>0</Value>
</CC>
<D>
<CID>2</CID>
<Value>514.15</Value>
</D>
<PD>
<CID>2</CID>
<Value>0</Value>
</PD>
<EBF>
<CID>2</CID>
<Value>0</Value>
</EBF>
<CST>
<DL>
<ATRID>13</ATRID>
<OB>
<CID>2</CID>
<Value>74.04</Value>
</OB>
<OC>
<CID>2</CID>
<Value>0.00</Value>
</OC>
<OS>
<CID>2</CID>
<Value>0.00</Value>
</OS>
<OF>
<CID>2</CID>
<Value>50.83</Value>
</OF>
<OP>
<CID>2</CID>
<Value>0.00</Value>
</OP>
<C>
<CID>2</CID>
<Value>0</Value>
</C>
<IBF>false</IBF>
<D>2014-06-09T14:57:53.521Z</D>
</DL>
</CST>
<CIT />
<CRMR xsi:nil="true" />
<CRM>
<CID>2</CID>
<Value>0</Value>
</CRM>
<TL ATC="Tax" PC="" DEN="User Development Fee - Arrival (UDF)">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>75.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Passenger Service Fee">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>146.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="User Development Fee - Departure (UDF)">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>1681.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Cute Fee">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>50.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Government Service Tax">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>151.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="User Development Fee - Arrival (UDF)">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>833.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Passenger Service Fee">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>1132.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="User Development Fee - Departure (UDF)">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>76.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Government Service Tax">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>148.00</Value>
</Amount>
</TL>
<PTSDPFS>
<PTSD IO="false">
<FBC>AP</FBC>
<ACD RBD="" ACCID="1" MCT="Super Sale Fare(AP)" INC="false" />
<ATSID xsi:nil="true" />
</PTSD>
</PTSDPFS>
<PTSDPFS>
<PTSD IO="false">
<FBC>AP</FBC>
<ACD RBD="" ACCID="1" MCT="Super Sale Fare(AP)" INC="false" />
<ATSID xsi:nil="true" />
</PTSD>
</PTSDPFS>
<RuleDetails>
<TRS xsi:nil="true" />
<PP xsi:nil="true" />
<II xsi:nil="true" />
<LTD xsi:nil="true" />
</RuleDetails>
</AirFareInfo>
输出。在这种情况下应该是AP,AP。如何提高性能?
正如我的评论所说,最好的答案取决于许多我不知道的因素。就像一个快速射击,你可以试试这个:data()
返回
SELECT REPLACE(FareDetails.query(N'data(/AirFareInfo/PTSDPFS/PTSD/FBC)').value('(text())[1]','nvarchar(100)'),' ',',')
FROM Table1
WHERE BookingID=123
XML方法都发现text()
分隔空白(遗憾我们不能改变这一点)。这个工作,只要你的文字<FBC>
不包含空白它自己。
这个答案是给出输出:AP,A而不是AP,AP –
@PiyushSing我试过了,我得到了正确的结果。也许这是削减某处(可变小,其他功能...) – Shnugo
嗨@shnugo,当我们在表中有重复的bookingid,那么它是失败的.eg BookingID 123来了4次在表和FareDetails(xml) FBC的值a,b,c,d对应于4 xml。 对于所有4个BookingID,输出应该是a,b,c,d。 当前输出为第一个输入为b,第二个输入为c,第三个输入为d,第四个输入为d。请协助。 –
您的xml格式不正确,无法使用xml type.read this header“Nodes()方法应用程序和效率”https://www.simple-talk.com/sql/database-administration/manipulating -xml-data-in-sql-server/ 在xml操作中还有一件事情,但不能回想起来。 – KumarHarsh
*但是我在DB中有百万条记录* ...在XML中为数百万读取值**不能很快......但是您正在使用WHERE [BookingID] = 123'。您是否将此电话过滤到了几条记录? ''修复了(在这种情况下是两个)还是'1:n'? *此节点中的内容*是否可以修改,或者可能是某处的'1:n'?这是一次性行动吗?还是你必须做的事?您可以使用触发器。维护一个你想要搜索的值的边桌。 –
Shnugo
'AirTraveler.ID'从哪里来? – Shnugo