locked
Total Noob to XML - Basic Question RRS feed

  • Question

  • Hi,

    Hope someone can help. I have done a fair bit of TSQL but am new to querying XML using SQL and am struggling with some basic concepts.

    I have a database with 2 fields, first is "ID", second is "ProfileXML".

    The ProfileXML field looks like this (cut down)...

    <Profile xmlns="urn:xxxxxxxx">
      <DatabaseRowVersion>"xxxxxxxx="</DatabaseRowVersion>
      <Id>xxxxxxxx</Id>
      <Organization Id="xxxxxxxx" />
      <SecurityPrincipal xmlns:q1="xxxxxxxx" xmlns:p3="xxxxxxxx" p3:type="xxxxxxxx" Name="John Smith" UserName="xxxxxxxx" Email="xxxxxxxx">
      </SecurityPrincipal>
    </Profile>

    ...I'm trying to return the ID field and the "Name" value from the ProfileXML field and have written this...

    SELECT  ProfileID,
            ProfileXML.value('(Profile/SecurityPrincipal/name)[1]','VARCHAR(100)') AS Name
    FROM    Tablename

    ...but am getting nulls from the XML field in my results.

    I know it is a basic question, but we all have to start somewhere I guess.

    Cheers,

    Chris.


    Friday, October 16, 2015 10:44 AM

Answers

  • SELECT ProfileID
    ,ProfileXml
    INTO #TempResource4
    FROM dbo.Profile
    
    ;WITH XMLNAMESPACES ('urn:Schemas-Zap-BI-Policy' as q1, 'http://www.w3.org/2001/XMLSchema-instance' as p3, DEFAULT 'urn:Schemas-Zap-BI-Components-Security-Profile')
    
    SELECT a.ProfileId,
    b.ProfileXML.value('@Name[1]','varchar(100)') AS Name
    FROM #TempResource4 a
    CROSS APPLY a.ProfileXml.nodes('/Profile/SecurityPrincipal')b(ProfileXML)

    Also, name is different from Name. 

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, October 16, 2015 5:25 PM

All replies

  • Hi Chris,

    the solution to your problem seems to be very straight forward to me.

    You have made a mistake in your X-Path for querying the attribute.

    Profile/SecurityPrincipal/name

    searches for a XML element called "name" under "Security Principal". It would work if your XML was like this

    <Profile xmlns="urn:xxxxxxxx"> <DatabaseRowVersion>"xxxxxxxx="</DatabaseRowVersion> <Id>xxxxxxxx</Id> <Organization Id="xxxxxxxx" /> <SecurityPrincipal xmlns:q1="xxxxxxxx" xmlns:p3="xxxxxxxx" p3:type="xxxxxxxx" UserName="xxxxxxxx" Email="xxxxxxxx"> <Name>John Smith</Name></SecurityPrincipal> </Profile>


    To search for the Attribute you would have to write

    Profile/SecurityPrincipal[@name]

    instead.

    XML Elements all have its own tag like "Profile", "Database Row Version" in your example.
    Information contained in XML elements (within the starting tag) is called attributes.

    Cheers

    Martin



    • Edited by Martin Guth Friday, October 16, 2015 11:08 AM
    Friday, October 16, 2015 11:04 AM
  • see this illustration

    declare @t table
    (
    ProfileID int,
    ProfileXML xml
    )
    insert @t
    values(12345,'<Profile xmlns="urn:xxxxxxxx">
      <DatabaseRowVersion>"xxxxxxxx="</DatabaseRowVersion>
      <Id>xxxxxxxx</Id>
      <Organization Id="xxxxxxxx" />
      <SecurityPrincipal xmlns:q1="xxxxxxxx" xmlns:p3="xxxxxxxx" p3:type="xxxxxxxx" UserName="xxxxxxxx" Email="xxxxxxxx">
        <Name>John Smith</Name></SecurityPrincipal>
    </Profile>')
    
    ;WITH XMLNAMESPACES ('xxxxxxxx' as q1, 'xxxxxxxx' as p3, DEFAULT 'urn:xxxxxxxx')
    SELECT ProfileID,
    t.u.value('Name[1]','varchar(100)') AS Name
    FROM @t t
    CROSS APPLY ProfileXML.nodes('/Profile/SecurityPrincipal')t(u)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Russ Loski Friday, October 16, 2015 2:29 PM
    Friday, October 16, 2015 11:23 AM
  • Hi, thanks for the reply.

    I started out using CROSS APPLY when doing this and had it laid out the same way you have here, but without the WITH XMLNAMESPACES section.


    This works (if you remove the alias "t" from the FROM clause in your example), but when I apply it to my table it still produces Nulls :( Any ideas as to why?

    ;WITH XMLNAMESPACES ('urn:Schemas-xxxxx' as q1, 'http://www.w3.org/2001/XMLSchema-instance' as p3, DEFAULT 'urn:Schemas-xxxxx')
    SELECT a.ProfileId,
    t.u.value('(Name)[1]','varchar(100)') AS Name
    FROM #TempResource4 a
    CROSS APPLY a.ProfileXml.nodes('/Profile/SecurityPrincipal')t(u)

    Friday, October 16, 2015 1:01 PM
  • The DEFAULT has to be exactly the same as the value in this "attribute" "xmlns='urn:xxxxxxxx'"  urn:Schemas-xxxx is not the same as urn:xxxxxxxx.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, October 16, 2015 2:33 PM
  • Ok, here is my code...

    SELECT ProfileID
    ,ProfileXml
    INTO #TempResource4
    FROM dbo.Profile
    
    ;WITH XMLNAMESPACES ('urn:Schemas-Zap-BI-Policy' as q1, 'http://www.w3.org/2001/XMLSchema-instance' as p3, DEFAULT 'urn:Schemas-Zap-BI-Components-Security-Profile')
    
    SELECT a.ProfileId,
    b.ProfileXML.value('Name[1]','varchar(100)') AS Name
    FROM #TempResource4 a
    CROSS APPLY a.ProfileXml.nodes('/Profile/SecurityPrincipal')b(ProfileXML)

    ...the text in quotes before "as q1" in the above is exactly as the text following "<SecurityPrincipal xmlns:q1=" in the XML

    ...the text in quotes before "as q3" in the above is exactly as the text following "xmlns:p3=" in the XML

    ...the text in quotes after "DEFAULT" in the above is exactly as the text following "<Profile xmlns=" at the start of the XML

    I believe this is what you mean and as such should be correct. I could be wrong, it has happened before ;)

    Friday, October 16, 2015 3:58 PM
  • There wouldn't happened to be an xmlns "attribute" (namespace declaration) in the SecurityPrincipal node?

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, October 16, 2015 4:34 PM
  • SELECT ProfileID
    ,ProfileXml
    INTO #TempResource4
    FROM dbo.Profile
    
    ;WITH XMLNAMESPACES ('urn:Schemas-Zap-BI-Policy' as q1, 'http://www.w3.org/2001/XMLSchema-instance' as p3, DEFAULT 'urn:Schemas-Zap-BI-Components-Security-Profile')
    
    SELECT a.ProfileId,
    b.ProfileXML.value('@Name[1]','varchar(100)') AS Name
    FROM #TempResource4 a
    CROSS APPLY a.ProfileXml.nodes('/Profile/SecurityPrincipal')b(ProfileXML)

    Also, name is different from Name. 

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, October 16, 2015 5:25 PM