CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Ram Das
Ram Das

Reputation: 358

How to parse this XML data

I have data like this,

can anyone please tell me how to parse this data..

  declare @tab xml


SET @tab ='<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
    xmlns:rs="urn:schemas-microsoft-com:rowset"
    xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
</s:Schema>
<rs:data>
<z:row Agents="434" c1="Adass" Keyvalue="401RE-422DS" Licensed="54" Overage="767" c5="[email protected]"
         c6="QCI of Des Moines"/>
    <z:row Agents="222" c1="Back setters" Keyvalue="4075QZ-3DCWD" Licensed="4533" Overage="9897"
         c5="[email protected]" c6="BBTech Solutions Inc"/>
    <z:row Agents="455" c1="gffs" Keyvalue="40343-5644Q" Licensed="212" Overage="655" c5="patrick"
         c6="new Dos"/>
         </rs:data>
</xml>'

How to select <row> Information like c5,Keyvalue,c1,c2

Thanks in advance.

Thanks @Alexander,@granada

Upvotes: 0

Views: 638

Answers (2)

granadaCoder
granadaCoder

Reputation: 27852

This should get you started:

declare @data xml


SET @data ='<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
    xmlns:rs="urn:schemas-microsoft-com:rowset"
    xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
    <s:ElementType name="row" content="eltOnly" rs:updatable="true">
        <s:AttributeType name="Agents" rs:number="6" rs:nullable="true" rs:maydefer="true" rs:writeunknown="true" rs:basetable="Sheet1$"
             rs:basecolumn="Agents">
            <s:datatype dt:type="float" dt:maxLength="8" rs:precision="15" rs:fixedlength="true"/>
        </s:AttributeType>
        <s:extends type="rs:rowbase"/>
    </s:ElementType>
</s:Schema>
<rs:data>
<z:row Agents="434" c1="Adass" Keyvalue="401RE-422DS" Licensed="54" Overage="767" c5="[email protected]"
         c6="QCI of Des Moines"/>
    <z:row Agents="222" c1="Back setters" Keyvalue="4075QZ-3DCWD" Licensed="4533" Overage="9897"
         c5="[email protected]" c6="BBTech Solutions Inc"/>
    <z:row Agents="455" c1="gffs" Keyvalue="40343-5644Q" Licensed="212" Overage="655" c5="patrick"
         c6="new Dos"/>
         </rs:data>
</xml>'



;
    WITH XMLNAMESPACES (
     '#RowsetSchema' as z , 'urn:schemas-microsoft-com:rowset' as rs
    )
SELECT T.parentEntity.value('(@c1)[1]', 'varchar(64)') AS c1Attribute,
       T.parentEntity.value('(@Keyvalue)[1]', 'varchar(64)') AS KeyvalueAttribute
FROM @data.nodes('//rs:data/z:row') AS T(parentEntity)
;

APPEND

If you want to dynamically read the attribute NAMES (and corresponding values), try this:

;
    WITH XMLNAMESPACES (
     '#RowsetSchema' as z , 'urn:schemas-microsoft-com:rowset' as rs
    )
       SELECT  
            CAST(x.v.query('local-name(.)') AS VARCHAR(128)) As TheAttributeName,
            v.value('.' , 'VARCHAR(128)') AS TheValue
            FROM @data.nodes('//rs:data/z:row/@*') x(v)
           ;

Upvotes: 1

Alexander Sigachov
Alexander Sigachov

Reputation: 1551

;WITH XMLNAMESPACES ('#RowsetSchema' AS z)
SELECT  
       Tbl.Col.value('@Agents', 'INT') as Agents,  
       Tbl.Col.value('@c1', 'varchar(50)') as c1,
       Tbl.Col.value('@Keyvalue', 'varchar(50)') as Keyvalue,
       Tbl.Col.value('@Licensed', 'INT') as Licensed,
       Tbl.Col.value('@Overage', 'INT') as Overage,
       Tbl.Col.value('@c5', 'varchar(150)') as c5,
       Tbl.Col.value('@c6', 'varchar(150)') as c6
FROM   @tab.nodes('//z:row') Tbl(Col)

Upvotes: 2

Related Questions