Extracting Sql: openxml

Showing posts with label openxml. Show all posts

Sunday, February 19, 2012

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"]')
>

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.googlegroups.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"]')
>

Friday, February 17, 2012

Extract values from XML column (rows) to a rowset

Hi,
I have a question regarding extracting XML data as relational data. From
the looks of it looks like the OPENXML statement is not relevant since the
data I need to extract resides in XML columns (in a table).
So I have this query
SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
MyXmlCol â' is an XML column, and the WHERE clause assures me that I know how
to access this XML data (using xpath)
Letâ's say the data in XML column looks like this
<MyStuff>
<Product>Windows Vista</Product>
<Version>1</Version>
</MyStuff>
(were in each row the value of the Product & Version nodes are different).
I want a table/rowset with two columns Product (xpath
/MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
I want the rowset/table to look like this:
Product Version
-- --
WindowsVista 1
WindowsHome 2
WindowsXP 3
How do I accomplish this ?
--
Asher.Asher
Take a look at this example to resturn Cities
declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
</AddressType> ');
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="222 Main"
City="SanDiego"
State="CA"
ZipCode = "2222" />
</AddressType> ');
SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
'varchar(50)' )
FROM @.tAddress
"Asher F." <asherfoa@.community.nospam> wrote in message
news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
> Hi,
> I have a question regarding extracting XML data as relational data. From
> the looks of it looks like the OPENXML statement is not relevant since the
> data I need to extract resides in XML columns (in a table).
> So I have this query
> SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
> MyXmlCol ? is an XML column, and the WHERE clause assures me that I know
> how
> to access this XML data (using xpath)
> Let?s say the data in XML column looks like this
> <MyStuff>
> <Product>Windows Vista</Product>
> <Version>1</Version>
> </MyStuff>
> (were in each row the value of the Product & Version nodes are different).
> I want a table/rowset with two columns Product (xpath
> /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
> I want the rowset/table to look like this:
> Product Version
> -- --
> WindowsVista 1
> WindowsHome 2
> WindowsXP 3
> How do I accomplish this ?
>
> --
> Asher.|||Hello Asher,
I understand that you'd like to insert a table from a xml type value. You
may want to use openxml to do the job:
CREATE TABLE MyStuff( product varchar(20), version int)
go
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'
<MyStuff>
<Product>Windows Vista</Product>
<Version>1</Version>
</MyStuff>
'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Mystuff
SELECT *
FROM OPENXML(@.docHandle, N'/MyStuff', 2)
WITH (proudct varchar(20) '/MyStuff/Product',
version int '/MyStuff/Version')
EXEC sp_xml_removedocument @.docHandle
select * from mystuff
If you have any comments or feedback, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Uri,
Thanks for the quick response, I understand the code you suggested and it
works for me 80% of times. I would very much appreciate your help in this
follow-up.
To extend your example, letâ's say I have this:
declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
<AddressRecord StreetLine1="222 Left"
City="New York"
State="NY"
ZipCode = "2222" />
</AddressType> ');
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="222 Main"
City="SanDiego"
State="CA"
ZipCode = "2222" />
</AddressType> ');
Note that the XML in the first row has two AddressRecord elements.
How do I make my result return a rowset with 3 rows representing all the
values:
SanDiego
New York
Houston
Thanks in advance!
--
Asher.
"Uri Dimant" wrote:
> Asher
> Take a look at this example to resturn Cities
> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> </AddressType> ');
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="222 Main"
> City="SanDiego"
> State="CA"
> ZipCode = "2222" />
> </AddressType> ');
> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
> 'varchar(50)' )
> FROM @.tAddress
>
> "Asher F." <asherfoa@.community.nospam> wrote in message
> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
> > Hi,
> > I have a question regarding extracting XML data as relational data. From
> > the looks of it looks like the OPENXML statement is not relevant since the
> > data I need to extract resides in XML columns (in a table).
> > So I have this query
> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
> > MyXmlCol â' is an XML column, and the WHERE clause assures me that I know
> > how
> > to access this XML data (using xpath)
> > Letâ's say the data in XML column looks like this
> > <MyStuff>
> > <Product>Windows Vista</Product>
> > <Version>1</Version>
> > </MyStuff>
> > (were in each row the value of the Product & Version nodes are different).
> > I want a table/rowset with two columns Product (xpath
> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
> > I want the rowset/table to look like this:
> > Product Version
> > -- --
> > WindowsVista 1
> > WindowsHome 2
> > WindowsXP 3
> >
> > How do I accomplish this ?
> >
> >
> >
> > --
> > Asher.
>
>|||Hello Peter,
Thank you for your response.
What I am trying to do is to extract data from an XML column I have in one
of my tables. I am trying to make the data in the XML column appear as part
of the normal rowset.
I will run a SQL SELECT statement from my C# code expecting to receive the
data back in the form of a rowset (so I can read it as primitive types using
IDataReader).
Anyway, Uri's response works for me in most of the cases, it doesn't cover
(and I tried several things myself) the case where I need to extract multiple
values (i.e. rows) from a single XML document (stored in the xml column in a
given row in my table).
Thanks for the help
--
Asher.
"Peter Yang [MSFT]" wrote:
> Hello Asher,
> I understand that you'd like to insert a table from a xml type value. You
> may want to use openxml to do the job:
> CREATE TABLE MyStuff( product varchar(20), version int)
> go
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'
> <MyStuff>
> <Product>Windows Vista</Product>
> <Version>1</Version>
> </MyStuff>
> '
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- Use OPENXML to provide rowset consisting of customer data.
> INSERT Mystuff
> SELECT *
> FROM OPENXML(@.docHandle, N'/MyStuff', 2)
> WITH (proudct varchar(20) '/MyStuff/Product',
> version int '/MyStuff/Version')
> EXEC sp_xml_removedocument @.docHandle
> select * from mystuff
>
> If you have any comments or feedback, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Asher
I see what you mean
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
<AddressRecord StreetLine1="111 Left"
City="New_York"
State="NY"
ZipCode = "3333" />
</AddressType> '
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
WITH (City varchar(10))
"Asher F." <asherfoa@.community.nospam> wrote in message
news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
> Hi Uri,
> Thanks for the quick response, I understand the code you suggested and it
> works for me 80% of times. I would very much appreciate your help in this
> follow-up.
> To extend your example, let?s say I have this:
> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> <AddressRecord StreetLine1="222 Left"
> City="New York"
> State="NY"
> ZipCode = "2222" />
> </AddressType> ');
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="222 Main"
> City="SanDiego"
> State="CA"
> ZipCode = "2222" />
> </AddressType> ');
> Note that the XML in the first row has two AddressRecord elements.
> How do I make my result return a rowset with 3 rows representing all the
> values:
> SanDiego
> New York
> Houston
> Thanks in advance!
> --
> Asher.
>
> "Uri Dimant" wrote:
>> Asher
>> Take a look at this example to resturn Cities
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)' )
>> FROM @.tAddress
>>
>> "Asher F." <asherfoa@.community.nospam> wrote in message
>> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> > Hi,
>> > I have a question regarding extracting XML data as relational data.
>> > From
>> > the looks of it looks like the OPENXML statement is not relevant since
>> > the
>> > data I need to extract resides in XML columns (in a table).
>> > So I have this query
>> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> > MyXmlCol ? is an XML column, and the WHERE clause assures me that I
>> > know
>> > how
>> > to access this XML data (using xpath)
>> > Let?s say the data in XML column looks like this
>> > <MyStuff>
>> > <Product>Windows Vista</Product>
>> > <Version>1</Version>
>> > </MyStuff>
>> > (were in each row the value of the Product & Version nodes are
>> > different).
>> > I want a table/rowset with two columns Product (xpath
>> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
>> > I want the rowset/table to look like this:
>> > Product Version
>> > -- --
>> > WindowsVista 1
>> > WindowsHome 2
>> > WindowsXP 3
>> >
>> > How do I accomplish this ?
>> >
>> >
>> >
>> > --
>> > Asher.
>>|||Or
SELECT address.value('(/AddressType/AddressRecord/@.City)[1]', 'varchar(50)')
FROM @.tAddress
UNION ALL
SELECT address.value('(/AddressType/AddressRecord/@.City)[2]', 'varchar(50)')
FROM @.tAddress
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O9Qv3C1THHA.5108@.TK2MSFTNGP06.phx.gbl...
> Asher
> I see what you mean
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> <AddressRecord StreetLine1="111 Left"
> City="New_York"
> State="NY"
> ZipCode = "3333" />
> </AddressType> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
> WITH (City varchar(10))
>
>
>
> "Asher F." <asherfoa@.community.nospam> wrote in message
> news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
>> Hi Uri,
>> Thanks for the quick response, I understand the code you suggested and it
>> works for me 80% of times. I would very much appreciate your help in this
>> follow-up.
>> To extend your example, let?s say I have this:
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> <AddressRecord StreetLine1="222 Left"
>> City="New York"
>> State="NY"
>> ZipCode = "2222" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> Note that the XML in the first row has two AddressRecord elements.
>> How do I make my result return a rowset with 3 rows representing all the
>> values:
>> SanDiego
>> New York
>> Houston
>> Thanks in advance!
>> --
>> Asher.
>>
>> "Uri Dimant" wrote:
>> Asher
>> Take a look at this example to resturn Cities
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)' )
>> FROM @.tAddress
>>
>> "Asher F." <asherfoa@.community.nospam> wrote in message
>> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> > Hi,
>> > I have a question regarding extracting XML data as relational data.
>> > From
>> > the looks of it looks like the OPENXML statement is not relevant since
>> > the
>> > data I need to extract resides in XML columns (in a table).
>> > So I have this query
>> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> > MyXmlCol ? is an XML column, and the WHERE clause assures me that I
>> > know
>> > how
>> > to access this XML data (using xpath)
>> > Let?s say the data in XML column looks like this
>> > <MyStuff>
>> > <Product>Windows Vista</Product>
>> > <Version>1</Version>
>> > </MyStuff>
>> > (were in each row the value of the Product & Version nodes are
>> > different).
>> > I want a table/rowset with two columns Product (xpath
>> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
>> > I want the rowset/table to look like this:
>> > Product Version
>> > -- --
>> > WindowsVista 1
>> > WindowsHome 2
>> > WindowsXP 3
>> >
>> > How do I accomplish this ?
>> >
>> >
>> >
>> > --
>> > Asher.
>>
>|||Hi Uri,
The OPENXML is probably not the solution since I have the XML document
stored in a column.
As for your second solution, it looks like it is in the right direction,
however I don't see how I can scale it to the case I have N (or an unkown
number of) AddressRecord nodes in each XML document (i.e. one row's XML
column might contain zero AddressRecord elements and another row might
contain 10...)
How do I scale this solution ?
--
Asher.
"Uri Dimant" wrote:
> Or
> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]', 'varchar(50)')
> FROM @.tAddress
> UNION ALL
> SELECT address.value('(/AddressType/AddressRecord/@.City)[2]', 'varchar(50)')
> FROM @.tAddress
>
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O9Qv3C1THHA.5108@.TK2MSFTNGP06.phx.gbl...
> > Asher
> > I see what you mean
> >
> > DECLARE @.idoc int
> >
> > DECLARE @.doc varchar(1000)
> >
> > SET @.doc ='
> >
> > <AddressType name="Shipping">
> >
> > <AddressRecord StreetLine1="111 Main"
> >
> > City="Houston"
> >
> > State="TX"
> >
> > ZipCode = "11111" />
> >
> > <AddressRecord StreetLine1="111 Left"
> >
> > City="New_York"
> >
> > State="NY"
> >
> > ZipCode = "3333" />
> >
> > </AddressType> '
> >
> > EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> >
> > SELECT *
> >
> > FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
> >
> > WITH (City varchar(10))
> >
> >
> >
> >
> >
> >
> >
> > "Asher F." <asherfoa@.community.nospam> wrote in message
> > news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
> >> Hi Uri,
> >> Thanks for the quick response, I understand the code you suggested and it
> >> works for me 80% of times. I would very much appreciate your help in this
> >> follow-up.
> >>
> >> To extend your example, letâ's say I have this:
> >>
> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="111 Main"
> >>
> >> City="Houston"
> >>
> >> State="TX"
> >>
> >> ZipCode = "11111" />
> >> <AddressRecord StreetLine1="222 Left"
> >>
> >> City="New York"
> >>
> >> State="NY"
> >>
> >> ZipCode = "2222" />
> >>
> >> </AddressType> ');
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="222 Main"
> >>
> >> City="SanDiego"
> >>
> >> State="CA"
> >>
> >> ZipCode = "2222" />
> >>
> >> </AddressType> ');
> >>
> >> Note that the XML in the first row has two AddressRecord elements.
> >> How do I make my result return a rowset with 3 rows representing all the
> >> values:
> >> SanDiego
> >> New York
> >> Houston
> >>
> >> Thanks in advance!
> >> --
> >> Asher.
> >>
> >>
> >> "Uri Dimant" wrote:
> >>
> >> Asher
> >> Take a look at this example to resturn Cities
> >>
> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="111 Main"
> >>
> >> City="Houston"
> >>
> >> State="TX"
> >>
> >> ZipCode = "11111" />
> >>
> >> </AddressType> ');
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="222 Main"
> >>
> >> City="SanDiego"
> >>
> >> State="CA"
> >>
> >> ZipCode = "2222" />
> >>
> >> </AddressType> ');
> >>
> >> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
> >> 'varchar(50)' )
> >>
> >> FROM @.tAddress
> >>
> >>
> >>
> >> "Asher F." <asherfoa@.community.nospam> wrote in message
> >> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
> >> > Hi,
> >> > I have a question regarding extracting XML data as relational data.
> >> > From
> >> > the looks of it looks like the OPENXML statement is not relevant since
> >> > the
> >> > data I need to extract resides in XML columns (in a table).
> >> > So I have this query
> >> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
> >> > MyXmlCol â' is an XML column, and the WHERE clause assures me that I
> >> > know
> >> > how
> >> > to access this XML data (using xpath)
> >> > Letâ's say the data in XML column looks like this
> >> > <MyStuff>
> >> > <Product>Windows Vista</Product>
> >> > <Version>1</Version>
> >> > </MyStuff>
> >> > (were in each row the value of the Product & Version nodes are
> >> > different).
> >> > I want a table/rowset with two columns Product (xpath
> >> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
> >> > I want the rowset/table to look like this:
> >> > Product Version
> >> > -- --
> >> > WindowsVista 1
> >> > WindowsHome 2
> >> > WindowsXP 3
> >> >
> >> > How do I accomplish this ?
> >> >
> >> >
> >> >
> >> > --
> >> > Asher.
> >>
> >>
> >>
> >
> >
>
>|||Check out the nodes method. It returns a table with one column of datatype XML. So if the XML
instance is a column inside a table, you use this with APPLY against the table where the column
exists. Example, assuming table is temp table instead of table variable:
SELECT nc.value('@.City[1]', 'varchar(39)')
FROM #tAddress
CROSS APPLY Address.nodes('/AddressType/AddressRecord') AS nt(nc)
---
Houston
New York
SanDiego
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Asher F." <asherfoa@.community.nospam> wrote in message
news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
> Hi Uri,
> Thanks for the quick response, I understand the code you suggested and it
> works for me 80% of times. I would very much appreciate your help in this
> follow-up.
> To extend your example, let's say I have this:
> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> <AddressRecord StreetLine1="222 Left"
> City="New York"
> State="NY"
> ZipCode = "2222" />
> </AddressType> ');
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="222 Main"
> City="SanDiego"
> State="CA"
> ZipCode = "2222" />
> </AddressType> ');
> Note that the XML in the first row has two AddressRecord elements.
> How do I make my result return a rowset with 3 rows representing all the
> values:
> SanDiego
> New York
> Houston
> Thanks in advance!
> --
> Asher.
>
> "Uri Dimant" wrote:
>> Asher
>> Take a look at this example to resturn Cities
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)' )
>> FROM @.tAddress
>>
>> "Asher F." <asherfoa@.community.nospam> wrote in message
>> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> > Hi,
>> > I have a question regarding extracting XML data as relational data. From
>> > the looks of it looks like the OPENXML statement is not relevant since the
>> > data I need to extract resides in XML columns (in a table).
>> > So I have this query
>> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> > MyXmlCol - is an XML column, and the WHERE clause assures me that I know
>> > how
>> > to access this XML data (using xpath)
>> > Let's say the data in XML column looks like this
>> > <MyStuff>
>> > <Product>Windows Vista</Product>
>> > <Version>1</Version>
>> > </MyStuff>
>> > (were in each row the value of the Product & Version nodes are different).
>> > I want a table/rowset with two columns Product (xpath
>> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
>> > I want the rowset/table to look like this:
>> > Product Version
>> > -- --
>> > WindowsVista 1
>> > WindowsHome 2
>> > WindowsXP 3
>> >
>> > How do I accomplish this ?
>> >
>> >
>> >
>> > --
>> > Asher.
>>|||See Tibor's reply , it is what you wanted
"Asher F." <asherfoa@.community.nospam> wrote in message
news:38DFF454-06D5-4E00-B623-F5FB216D87EC@.microsoft.com...
> Hi Uri,
> The OPENXML is probably not the solution since I have the XML document
> stored in a column.
> As for your second solution, it looks like it is in the right direction,
> however I don't see how I can scale it to the case I have N (or an unkown
> number of) AddressRecord nodes in each XML document (i.e. one row's XML
> column might contain zero AddressRecord elements and another row might
> contain 10...)
> How do I scale this solution ?
> --
> Asher.
>
> "Uri Dimant" wrote:
>> Or
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)')
>> FROM @.tAddress
>> UNION ALL
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[2]',
>> 'varchar(50)')
>> FROM @.tAddress
>>
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:O9Qv3C1THHA.5108@.TK2MSFTNGP06.phx.gbl...
>> > Asher
>> > I see what you mean
>> >
>> > DECLARE @.idoc int
>> >
>> > DECLARE @.doc varchar(1000)
>> >
>> > SET @.doc ='
>> >
>> > <AddressType name="Shipping">
>> >
>> > <AddressRecord StreetLine1="111 Main"
>> >
>> > City="Houston"
>> >
>> > State="TX"
>> >
>> > ZipCode = "11111" />
>> >
>> > <AddressRecord StreetLine1="111 Left"
>> >
>> > City="New_York"
>> >
>> > State="NY"
>> >
>> > ZipCode = "3333" />
>> >
>> > </AddressType> '
>> >
>> > EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
>> >
>> > SELECT *
>> >
>> > FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
>> >
>> > WITH (City varchar(10))
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Asher F." <asherfoa@.community.nospam> wrote in message
>> > news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
>> >> Hi Uri,
>> >> Thanks for the quick response, I understand the code you suggested and
>> >> it
>> >> works for me 80% of times. I would very much appreciate your help in
>> >> this
>> >> follow-up.
>> >>
>> >> To extend your example, let?s say I have this:
>> >>
>> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="111 Main"
>> >>
>> >> City="Houston"
>> >>
>> >> State="TX"
>> >>
>> >> ZipCode = "11111" />
>> >> <AddressRecord StreetLine1="222 Left"
>> >>
>> >> City="New York"
>> >>
>> >> State="NY"
>> >>
>> >> ZipCode = "2222" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="222 Main"
>> >>
>> >> City="SanDiego"
>> >>
>> >> State="CA"
>> >>
>> >> ZipCode = "2222" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> Note that the XML in the first row has two AddressRecord elements.
>> >> How do I make my result return a rowset with 3 rows representing all
>> >> the
>> >> values:
>> >> SanDiego
>> >> New York
>> >> Houston
>> >>
>> >> Thanks in advance!
>> >> --
>> >> Asher.
>> >>
>> >>
>> >> "Uri Dimant" wrote:
>> >>
>> >> Asher
>> >> Take a look at this example to resturn Cities
>> >>
>> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="111 Main"
>> >>
>> >> City="Houston"
>> >>
>> >> State="TX"
>> >>
>> >> ZipCode = "11111" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="222 Main"
>> >>
>> >> City="SanDiego"
>> >>
>> >> State="CA"
>> >>
>> >> ZipCode = "2222" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> >> 'varchar(50)' )
>> >>
>> >> FROM @.tAddress
>> >>
>> >>
>> >>
>> >> "Asher F." <asherfoa@.community.nospam> wrote in message
>> >> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> >> > Hi,
>> >> > I have a question regarding extracting XML data as relational data.
>> >> > From
>> >> > the looks of it looks like the OPENXML statement is not relevant
>> >> > since
>> >> > the
>> >> > data I need to extract resides in XML columns (in a table).
>> >> > So I have this query
>> >> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> >> > MyXmlCol ? is an XML column, and the WHERE clause assures me that I
>> >> > know
>> >> > how
>> >> > to access this XML data (using xpath)
>> >> > Let?s say the data in XML column looks like this
>> >> > <MyStuff>
>> >> > <Product>Windows Vista</Product>
>> >> > <Version>1</Version>
>> >> > </MyStuff>
>> >> > (were in each row the value of the Product & Version nodes are
>> >> > different).
>> >> > I want a table/rowset with two columns Product (xpath
>> >> > /MyStuff/Product/text()) and Version (xpath
>> >> > /MyStuff/Version/text())
>> >> > I want the rowset/table to look like this:
>> >> > Product Version
>> >> > -- --
>> >> > WindowsVista 1
>> >> > WindowsHome 2
>> >> > WindowsXP 3
>> >> >
>> >> > How do I accomplish this ?
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Asher.
>> >>
>> >>
>> >>
>> >
>> >
>>|||Did you look at my suggestion? It produces exactly the result you asked for...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Asher F." <asherfoa@.community.nospam> wrote in message
news:161F4EC4-7C19-430D-91EF-D2684D840D9D@.microsoft.com...
> Hello Peter,
> Thank you for your response.
> What I am trying to do is to extract data from an XML column I have in one
> of my tables. I am trying to make the data in the XML column appear as part
> of the normal rowset.
> I will run a SQL SELECT statement from my C# code expecting to receive the
> data back in the form of a rowset (so I can read it as primitive types using
> IDataReader).
> Anyway, Uri's response works for me in most of the cases, it doesn't cover
> (and I tried several things myself) the case where I need to extract multiple
> values (i.e. rows) from a single XML document (stored in the xml column in a
> given row in my table).
> Thanks for the help
> --
> Asher.
>
> "Peter Yang [MSFT]" wrote:
>> Hello Asher,
>> I understand that you'd like to insert a table from a xml type value. You
>> may want to use openxml to do the job:
>> CREATE TABLE MyStuff( product varchar(20), version int)
>> go
>> DECLARE @.docHandle int
>> declare @.xmlDocument xml
>> set @.xmlDocument = N'
>> <MyStuff>
>> <Product>Windows Vista</Product>
>> <Version>1</Version>
>> </MyStuff>
>> '
>> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
>> -- Use OPENXML to provide rowset consisting of customer data.
>> INSERT Mystuff
>> SELECT *
>> FROM OPENXML(@.docHandle, N'/MyStuff', 2)
>> WITH (proudct varchar(20) '/MyStuff/Product',
>> version int '/MyStuff/Version')
>> EXEC sp_xml_removedocument @.docHandle
>> select * from mystuff
>>
>> If you have any comments or feedback, please feel free to let's know. Thank
>> you.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Community Support
>> ==================================================>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>> ications
>> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no rights.
>>|||Thank you Tibor, it looks like exactlly what I need.
I am now reading the relevant documentation about the functions you used so
I can get a deeper understanding.
Thank you very much for your help!
--
Asher.
"Tibor Karaszi" wrote:
> Did you look at my suggestion? It produces exactly the result you asked for...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Asher F." <asherfoa@.community.nospam> wrote in message
> news:161F4EC4-7C19-430D-91EF-D2684D840D9D@.microsoft.com...
> > Hello Peter,
> > Thank you for your response.
> > What I am trying to do is to extract data from an XML column I have in one
> > of my tables. I am trying to make the data in the XML column appear as part
> > of the normal rowset.
> > I will run a SQL SELECT statement from my C# code expecting to receive the
> > data back in the form of a rowset (so I can read it as primitive types using
> > IDataReader).
> > Anyway, Uri's response works for me in most of the cases, it doesn't cover
> > (and I tried several things myself) the case where I need to extract multiple
> > values (i.e. rows) from a single XML document (stored in the xml column in a
> > given row in my table).
> >
> > Thanks for the help
> >
> > --
> > Asher.
> >
> >
> > "Peter Yang [MSFT]" wrote:
> >
> >> Hello Asher,
> >>
> >> I understand that you'd like to insert a table from a xml type value. You
> >> may want to use openxml to do the job:
> >>
> >> CREATE TABLE MyStuff( product varchar(20), version int)
> >> go
> >>
> >> DECLARE @.docHandle int
> >> declare @.xmlDocument xml
> >> set @.xmlDocument = N'
> >> <MyStuff>
> >> <Product>Windows Vista</Product>
> >> <Version>1</Version>
> >> </MyStuff>
> >> '
> >> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> >> -- Use OPENXML to provide rowset consisting of customer data.
> >> INSERT Mystuff
> >> SELECT *
> >> FROM OPENXML(@.docHandle, N'/MyStuff', 2)
> >> WITH (proudct varchar(20) '/MyStuff/Product',
> >> version int '/MyStuff/Version')
> >>
> >> EXEC sp_xml_removedocument @.docHandle
> >>
> >> select * from mystuff
> >>
> >>
> >> If you have any comments or feedback, please feel free to let's know. Thank
> >> you.
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Community Support
> >> ==================================================> >> Get notification to my posts through email? Please refer to
> >> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> >> ications
> >> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> >> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> >> where an initial response from the community or a Microsoft Support
> >> Engineer within 1 business day is acceptable. Please note that each follow
> >> up response may take approximately 2 business days as the support
> >> professional working with you may need further investigation to reach the
> >> most efficient resolution. The offering is not appropriate for situations
> >> that require urgent, real-time or phone-based interactions or complex
> >> project analysis and dump analysis issues. Issues of this nature are best
> >> handled working with a dedicated Microsoft Support Engineer by contacting
> >> Microsoft Customer Support Services (CSS) at
> >> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> >> ==================================================> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >>
>

Sunday, February 19, 2012

Extracting href from XML using OpenXML

Extracting href from XML using OpenXML

Friday, February 17, 2012

Extract values from XML column (rows) to a rowset

Extract values from XML column (rows) to a rowset

Extracting Sql

Blog Archive

About Me