Re: XML parse - sql server 2008

rsocol — Mon, 24 Oct 2011 13:34:23 GMT

Din păcate XML-ul tău nu se vede prea bine (am noroc că sunt moderator și am putut să-i dau "Edit" ca să văd XML-ul).

Oricum, ideea e că acel [28] nu-i spune că vrei să-ți dea nodul care are atributul "m" egal cu 28, ci că vrei al 28-lea nod de genul respectiv. De altfel, poți verifica treaba asta dacă încerci cu [12]: nu îți rezultă valoarea "224", ci "2". Pentru ceea ce vrei tu, încearcă ceva de genul:

SELECT XMLRECORD.value('row[1]/c167[@m=12][1]', 'varchar(100)'), * from TBL

Răzvan

XML parse - sql server 2008

Alex — Mon, 24 Oct 2011 13:12:12 GMT

salut, am o coloana de tip XML intr-o tabela de sql server 2008 Exemplu

<row id="0800104829">
<c1>118035669</c1>
<c2>RON</c2>
<c3>1</c3>
<c4>28642.23</c4>
<c5>RO</c5>
<c6>20071219</c6>
<c7>20141218</c7>
<c8>2010.01</c8>
<c11>21062</c11>
<c12>1180356690100</c12>
<c13>1</c13>
<c14>B</c14>
<c15>1</c15>
<c16>12.21</c16>
<c19>YES</c19>
<c23>NO</c23>
<c24>301.84</c24>
<c33>1</c33>
<c40>20111018</c40>
<c41>20111021</c41>
<c42>NO</c42>
<c44>SEMI-AUTOMATIC</c44>
<c45>CUR</c45>
<c52>TR</c52>
<c64>NORMAL</c64>
<c65>1</c65>
<c67>1180356690100</c67>
<c72>1180356690100</c72>
<c78>1180356690100</c78>
<c79>1451</c79>
<c81>NO</c81>
<c84>20071219</c84>
<c86>RO</c86>
<c87>RO</c87>
<c89>AUTOMATIC</c89>
<c90>CUR</c90>
<c101>50000.00</c101>
<c104>50000</c104>
<c106>0</c106>
<c110>100</c110>
<c111>NO</c111>
<c112>100</c112>
<c113>28642.23</c113>
<c115>NO</c115>
<c123>1180356690100</c123>
<c134>20071219</c134>
<c142>ACT CRT</c142>
<c143>MERGE</c143>
<c144>NU E CAZUL</c144>
<c145>NO</c145>
<c146>NO</c146>
<c147>AMORT</c147>
<c153>1020</c153>
<c153 m="2">2010</c153>
<c154>D20111021012556145115</c154>
<c154 m="2">D20111021012556145116</c154>
<c159>NO</c159>
<c160>Y</c160>
<c167>20071219</c167>
<c167 m="2">Nu este cazul</c167>
<c167 m="3">BM</c167>
<c167 m="4">NU E CAZUL</c167>
<c167 m="5">FIDEJ</c167>
<c167 m="6">20100107</c167>
<c167 m="7">20071219</c167>
<c167 m="8">NT1006</c167>
<c167 m="12">224</c167>
<c167 m="16">st (-)</c167>
<c167 m="17">,LC</c167>
<c167 m="20">2</c167>
<c167 m="21">237</c167>
<c167 m="22">-2.29</c167>
<c167 m="29">14.5</c167>
<c167 m="31">50000.00</c167>
<c167 m="35">12.21</c167>
<c167 m="37">20071218</c167>
<c167 m="38">18001414</c167>
<c167 m="39">25</c167>
<c167 m="41">3</c167>
<c167 m="42">17</c167>
<c167 m="43">ULTIM TRIM</c167>
<c167 m="44">6</c167>
<c167 m="48">11.Altele</c167>
<c167 m="49">N1006</c167>
<c167 m="60">OK</c167>
<c167 m="70">11-Altele</c167>
<c167 m="80">20120118</c167>
<c167 m="82">OK</c167>
<c167 m="91">
<c171>END</c171>
<c173>915.23</c173>
<c197>NO</c197>
<c204>1</c204>
<c205>MID</c205>
<c207>NO</c207>
<c208>NO</c208>
<c217>00</c217>
<c218>NO</c218>
<c233>1599011408388.00</c233>
<c233 m="2">1-3</c233>
<c233 m="3">1</c233>
<c234>SCHED.RE.DEFINED}SCHEDULES WILL BE RE-DEFINED</c234>
<c236>112</c236>
<c237>1239_DH</c237>
<c237 m="2">1140_CB</c237>
<c237 m="3">1140_CB</c237>
<c237 m="4">1131_CB</c237>
<c237 m="5">1131_CB</c237>
<c237 m="6">1072_CB</c237>
<c237 m="7">1072_CB</c237>
<c237 m="8">1071_CB</c237>
<c237 m="9">1071_CB</c237>
<c237 m="10">1066_CB</c237>
<c237 m="11">1066_CB</c237>
<c237 m="12">1064_CB</c237>
<c237 m="13">1064_CB</c237>
<c237 m="14">1082_CB</c237>
<c237 m="15">1082_CB</c237>
<c237 m="16">1082_CB</c237>
<c237 m="17">1082_CB</c237>
<c237 m="18">1227_AN</c237>
<c238>1110211704</c238>
<c239>1255_GT</c239>
<c240>RO0</c240>
<c241>1093</c241>
<c243>
</c243></c167></row>
--editat de Andrei Ignat

daca apelez tabela asa select XMLRECORD.value('row[1]/c167[28]', 'varchar(100)'), * from TBL iar tagul 28 nu exista in loc sa-mi intoarca null am valoarea de la tagul 70 adica "11-Altele" si nu inteleg de ce? cum trebuie sa apelez aceasta coloana de timp xml? select XMLRECORD.value('row[1]/c167[22]', 'money'), * from TBL imi intoarce valoarea de la m=42. problema este doar la campurile multivalue. multumesc.

T-SQL

Re: XML parse - sql server 2008

XML parse - sql server 2008