Welcome to Sign in | Help
in Search

XML parse - sql server 2008

Last post 10-24-2011, 4:34 PM by rsocol. 1 replies.
Sort Posts: Previous Next
  •  10-24-2011, 4:12 PM 8979

    XML parse - sql server 2008

    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.
  •  10-24-2011, 4:34 PM 8980 in reply to 8979

    Re: XML parse - sql server 2008

    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
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems