How to retrieve element values from xml have multiple namespaces from SQL Server?

Question

I was trying to retrieve hasFolder attribute values from below XML file and couldn't not working the query which i written. Could you help me to get the correct resultset.

Query which I have written is not working:

;with xmlnamespaces('http://www.omg.org/XMI' as xmi,
                    'rochade:scanboxi:150' as rim,
                    'http://www.asg.com/rochade/xml/xmiExtension/1.0' as rxe) 
select 
    data,
    data.value('(/xmi:XMI/rim:Repository/rim:name)[1]', 'varchar(max)')
from 
    Testxml

XML data:

<?xml version="1.0" encoding="UTF-8"?>
<xmi:XMI xmlns:xmi="http://www.omg.org/XMI" xmi:version="2.0" 
         xmlns:rim="rochade:scanboxi:150" 
         xmlns:rxe="http://www.asg.com/rochade/xml/xmiExtension/1.0"  >
    <xmi:Documentation>
        <xmi:exporter>Rochade SAX Import/Exporter</xmi:exporter>
        <xmi:exporterVersion>2.10.xx</xmi:exporterVersion>
        <xmi:Extension extender="ASG-Rochade XML Facilities">
            <rxe:importModes>
                <mode category="ATTRIBUTE">KEEP</mode>
                <mode category="ATTRIBUTE.*.BOXI_HAS_INSTANCE">REMOVE</mode>
                <mode category="ATTRIBUTE.BOXI_RESULT_OBJECT.BOXI_SOURCED_FROM_COLUMN">REMOVE</mode>
                <mode category="ATTRIBUTE.BOXI_CRYSTAL_COLUMN.BOXI_CR_SOURCE_COLUMN_REF">REMOVE</mode>
                <mode category="ATTRIBUTE.BOXI_CRYSTAL_TABLE.BOXI_CR_SOURCE_TABLE_REF">REMOVE</mode>
                <mode category="ATTRIBUTE.BOXI_WEBI_REPORT.BOXI_HAS_PROMPT">REMOVE</mode>
                <mode category="ATTRIBUTE.BOXI_REPORT.BOXI_HAS_REPORT_ELEMENT">REMOVE</mode>
            </rxe:importModes>
        </xmi:Extension>
    </xmi:Documentation>
    <rim:Repository  xmi:id="_1"  rim:name="@BOBJPROD" >
        <rim:hasFolder  xmi:type="rim:Folder"  xmi:id="_3550"  rim:name="/FOLDERS/AdHoc/ARIOT AdHoc/Finance - Arizona/DSS"  rim:shortName="DSS"  rim:cmsScanTime="20150911 - 17:00:46"  rim:cmsScanUser="TU01822"  rim:cmsCreTime="20120802 - 16:25:38"  rim:cmsModTime="20120802 - 16:25:38"  rim:sID="28341084"  rim:cUID="AdcZXEQ8xUFLgEtZyS9IW0Y"  rim:pCUID="AWtaU4UM3iRBkI_lYBnXbns" >
            <rim:desc></rim:desc>
            <rim:hasBoObj  xmi:type="rim:Webi"  xmi:id="_17218"  rim:name="RRIS - Modifier Report"  rim:owner="JXM10"  rim:author="JXM10"  rim:cmsCreTime="20120822 - 10:38:24"  rim:cmsModTime="20121228 - 12:03:46"  rim:cmsScanTime="20150911 - 17:00:46"  rim:cmsScanUser="TU01822"  rim:sID="28915038"  rim:cUID="ASQ.gXvnAt9FqI7.AH7Gg2k"  rim:pCUID="AdcZXEQ8xUFLgEtZyS9IW0Y"  rim:mustFillContexts="false"  rim:mustFillPassword="false"  rim:mustFillPrompts="false"  rim:docLocaleType="LOCALE"  rim:formatOption="Webi"  rim:repSize="170,616" >
            <rim:desc>Total Gross Revenue Report</rim:desc>
            <rim:scanStat>ScanState OK </rim:scanStat>
            <rim:scanMessages>ScanRc 0
            ScanTime 20150911 - 17:00:46
            NO ScanMessages.</rim:scanMessages>
            <rim:keywords>Stats, Patient Visits, Gross Revenue</rim:keywords>
            <rim:bindStat>UNBOUND</rim:bindStat>
            <rim:hasReportField  xmi:type="rim:ReportField"  xmi:id="_860105"  rim:name="Count"  rim:fldType="VariableExpression"  rim:qualification="MEASURE"  rim:formulaLangId="[Count]" >
            <rim:formula>=1</rim:formula>
            </rim:hasReportField>
            <rim:hasReportField  xmi:type="rim:ReportField"  xmi:id="_860058"  rim:name="DP0.CPT Code"  rim:fldType="DPExpression"  rim:qualification="DIMENSION"  rim:formulaLangId="[CPT Code]"  rim:unvKey="CPT.CPT Code" >
            <rim:calcRef  xmi:type="rim:ResultObj"  xmi:idref="_860223"  />
            </rim:hasReportField>
            <rim:hasReportField  xmi:type="rim:ReportField"  xmi:id="_860077"  rim:name="DP0.CPT Description"  rim:fldType="DPExpression"  rim:qualification="DIMENSION"  rim:formulaLangId="[CPT Description]"  rim:unvKey="CPT.CPT Description" >
            <rim:calcRef  xmi:type="rim:ResultObj"  xmi:idref="_860224"  />
            </rim:hasReportField>

<hasFolder> is an element - not an attribute ...

marc_s
– marc_s

2016-07-02 06:27:05 +00:00
Commented Jul 2, 2016 at 6:27 — marc_s
– marc_s, Commented Jul 2, 2016 at 6:27

marc_s · Accepted Answer · 2016-07-02 09:49:10Z

1

Your namespaces are all nicely and properly set up - that is not the problem. But you're mixing attributes and elements. The question asks about the hasFolder attribute - but that's really an element with a bunch of attributes - and the code you show is actually trying to read the name attribute but is addressing it as if it were an element.

If you want to retrieve the name=... attribute from the <rim:Repository> element, then you need to use this XQuery:

;with xmlnamespaces('http://www.omg.org/XMI' as xmi,
                    'rochade:scanboxi:150' as rim,
                    'http://www.asg.com/rochade/xml/xmiExtension/1.0' as rxe) 
select 
    data.value('(/xmi:XMI/rim:Repository/@rim:name)[1]', 'varchar(max)')
from 
    Testxml

You need to indicate that you're interested in an attribute by putting a @ at the start of its name.

If you want to retrieve the whole <rim:hasFolder> element as an XML fragment, then you need to use this XQuery:

;with xmlnamespaces('http://www.omg.org/XMI' as xmi,
                    'rochade:scanboxi:150' as rim,
                    'http://www.asg.com/rochade/xml/xmiExtension/1.0' as rxe) 
select 
    data.query('(/xmi:XMI/rim:Repository/rim:hasFolder)[1]')
from 
    Testxml

Update: if you want to retrieve a single attribute from the <rim:hasFolder> element, use this syntax:

If you want to retrieve the whole <rim:hasFolder> element as an XML fragment, then you need to use this XQuery:

;with xmlnamespaces('http://www.omg.org/XMI' as xmi,
                    'rochade:scanboxi:150' as rim,
                    'http://www.asg.com/rochade/xml/xmiExtension/1.0' as rxe) 
select 
    data.value('(/xmi:XMI/rim:Repository/rim:hasFolder/@rim:name)[1]', 'varchar(200)')
from 
    Testxml

and adapt as needed for other attributes

edited Jul 2, 2016 at 9:49

answered Jul 2, 2016 at 6:32

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anilkumar Yaaram Over a year ago

Thank you Marc. Can we convert hasFolder attributes into relationmodel in this query itself?

marc_s Over a year ago

@AnilkumarYaaram: updated my response to show how to access attributes from the <rim:hasFolder> node - adapt as needed

Anilkumar Yaaram Over a year ago

Sorry the question was wrong. with the value function would be getting only one record. but in my xml many records are there. For eg: <rim:hasBoObj> many entries are there in hasFolder. Couldn't retrieve multiple records. Do i need to work with any other function here to get complete data of this particular Element.

Collectives™ on Stack Overflow

How to retrieve element values from xml have multiple namespaces from SQL Server?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related