Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

extract XML using openXML

66 views

Skip to first unread message

Vinod

unread,

Mar 23, 2007, 11:15:31 AM3/23/07

Hi,
Please look at the full XML that I have pasted below. Can
anybody tell me how can I extract the "href" value from the
following userKeys tag "<userKeys href="#id27"/>" like the
way I extract id from <multiref id="id2">.

Thanks for the help
Vinod

select ID,
pageId,
docName,
docType,
url,
href,
corr_group_seq,
corr_cat_seq,
corr_subject_seq,
attach_filename,
template_desc,
entity_type_seq,
entity_id,
template_seq,
case_type_seq,
attach_date_ts,
case_id,
case_name,
attach_by_user_seq,corr_seq from OPENXML('<?xml
version="1.0" encoding="UTF-8"?><soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><soapenv:Body>
<ns1:getMatchingDocumentsResponse
soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:ns1="http://janoitsyst.ms.acs-shc.com:8080">
<getMatchingDocumentsReturn
soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
<getMatchingDocumentsReturn href="#id0"/>
</getMatchingDocumentsReturn>
</ns1:getMatchingDocumentsResponse>
<multiRef id="id2" soapenc:root="0"
soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xsi:type="ns10:DocfinityDocument"
xmlns:ns10="http://document.docfinity.ms.acs.com"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
<pageId xsi:type="soapenc:string">7</pageId>
<docName xsi:type="soapenc:string">3003</docName>
<docType xsi:type="soapenc:string">Image</docType>
<url
xsi:type="soapenc:string">http://janoitsyst.ms.acs-shc.com:8080/msMWServices/servlet/DocumentViewerServlet?pageId=7&
tempId=1174573869562&repositoryPath=//janoitsyst/TestImages/IMAGESTORE/0/7.tif</url>
<cascadeKeys href="#id26"/>
<userKeys href="#id27"/>
</multiRef>
<multiRef id="id27" soapenc:root="0"
soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xsi:type="ns20:Map"
xmlns:ns20="http://xml.apache.org/xml-soap"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"><item><key
xsi:type="soapenc:string">29</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">39</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">49</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">24</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">50</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">23</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">34</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">52</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">30</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">18</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">20</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">42</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">25</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">32</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">43</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">35</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">13</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">26</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">44</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">9</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">19</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">51</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">28</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">3</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">41</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">27</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">14</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">17</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">6</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">1</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">36</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">5</key><value
xsi:type="soapenc:string">RRI</value></item>
<item><key xsi:type="soapenc:string">16</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">46</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">37</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">55</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">33</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">11</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">15</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">4</key>
<value
xsi:type="soapenc:string">07003830030000030</value></item><item><key
xsi:type="soapenc:string">40</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">12</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">45</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">7</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">2</key><value
xsi:type="soapenc:string">CMS1500</value></item><item><key
xsi:type="soapenc:string">38</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">53</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">31</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">47</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">54</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">21</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">48</key>
<value xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">10</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">22</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">8</key>
<value
xsi:type="soapenc:string"></value></item></multiRef></soapenv:Body></soapenv:Envelope>'
,'//multiRef') with
("ID" char(10) '@id',
pageId char(20) 'pageId',
docName char(30) 'docName',
docType char(30) 'docType',
url varchar(500) 'url',
href varchar(20) '@href',
corr_group_seq char(32) 'item/value[../key="4"]',
corr_cat_seq char(32) 'item/value[../key="20"]',
corr_subject_seq char(32) 'item/value[../key="5"]',
attach_filename char(32) 'item/value[../key="17"]',
template_desc char(32) 'item/value[../key="6"]',
entity_type_seq char(32) 'item/value[../key="5"]',
entity_id char(32) 'item/value[../key="4"]',
template_seq char(32) 'item/value[../key="4"]',
case_type_seq char(32) 'item/value[../key="16"]',
attach_date_ts char(32) 'item/value[../key="13"]',
case_id char(32) 'item/value[../key="9"]',
case_name char(32) 'item/value[../key="11"]',
attach_by_user_seq char(32) 'item/value[../key="15"]',
corr_seq char(32) 'item/value[../key="2"]')

0 new messages