Extracting href from XML using OpenXML

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="http://links.10026.com/?link=#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
><soapenv:Body>
<ns1:getMatchingDocumentsResponse
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
<getMatchingDocumentsReturn
soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
<getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
</getMatchingDocumentsReturn>
</ns1:getMatchingDocumentsResponse>
<multiRef id="id2" soapenc:root="0"
soapenv:encodingxstyle="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
/0/7.tif<
/url>
<cascadeKeys href="http://links.10026.com/?link=#id26"/>
<userKeys href="http://links.10026.com/?link=#id27"/>
</multiRef>
<multiRef id="id27" soapenc:root="0"
soapenv:encodingxstyle="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"]')
Do you still need help with this?
Just a few tips:
1. You have to use sp_xml_preparedocument to parse the document (and use the
3rd parameter to provide namespace bindings) and then use the handle as
first parameter of OpenXML.
2. Do not forget to release the handle using sp_xml_removedocument
3. be sure that you find the performance of doing complex path expressions
in the row and column patterns. Otherwise use relational predicates instead.
Best regards
Michael
<vinodthomas@.hotmail.com> wrote in message
news:1174668434.957676.227050@.e65g2000hsc.googlegr oups.com...
> 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="http://links.10026.com/?link=#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
> <ns1:getMatchingDocumentsResponse
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> <getMatchingDocumentsReturn
> soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
> <getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
> </getMatchingDocumentsReturn>
> </ns1:getMatchingDocumentsResponse>
> <multiRef id="id2" soapenc:root="0"
> soapenv:encodingxstyle="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
> /0/7.tif<
> /url>
> <cascadeKeys href="http://links.10026.com/?link=#id26"/>
> <userKeys href="http://links.10026.com/?link=#id27"/>
> </multiRef>
> <multiRef id="id27" soapenc:root="0"
> soapenv:encodingxstyle="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/";
> 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"]')
>