Post reply

XML Data Type - Singleton issue - CROSS APPLY

  • April 24, 2009 at 12:06 pm

    #131518

    Hi all,

    I am having trouble locating the position/depth of the url attribuute on the /people/person/role level. My problem is I can dynamically figure out what int value to put in the [] singleton. If you look at the XML provided you will see various depths of where the url attribute is located. I can't change the XML. It would take an act of God and Congress.

    Help and Suggestions are appreciated. Please the attached .txt file as the site will not let me post the XML code for some reason

    IF OBJECT_ID('tempdb..#XML') IS NOT NULL

    DROP TABLE #XML

    CREATE TABLE #XML (ID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, Data xml NOT NULL)

    INSERT INTO #XML (Data) SELECT @xml

    CREATE PRIMARY XML INDEX PXML_#XML_Data ON #XML(Data)

    SELECT

    co1.value('@id','int') AS OfficialID,

    co1.value('@lastname','varchar(100)') AS LastName,

    co1.value('@firstname','varchar(100)') AS FirstName,

    co1.value('@birthday','varchar(15)') AS Birthday,

    co1.value('@gender','varchar(1)') AS Gender,

    co1.value('@religion','varchar(100)') AS Religion,

    co1.value('role[1]/@url','varchar(100)') AS URL,

    co1.value('@party','varchar(20)') AS Party,

    co1.value('@osid','varchar(20)') AS OSID,

    co1.value('@bioguideid','varchar(20)') AS BioGuideID,

    co1.value('@title','varchar(20)') AS Title,

    co1.value('@state','varchar(20)') AS State,

    co1.value('@district','int') AS District,

    co1.value('@name','varchar(100)') AS Name

    --INTO #Rep

    FROM #XML CROSS APPLY #XML.Data.nodes('/people/person') AS Feed(co1)

    DROP TABLE #XML

    [/code]

  • April 24, 2009 at 12:10 pm

    #983753

    XML disappears from the forum if you just put it straight in there. I think you can use a code tag with the XML type, or (definitely) you can attach XML in a text file that you upload.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 24, 2009 at 1:11 pm

    #983800

    Hi

    You have to specify the required @url in your path expression:

    co1.value('role[@url][1]/@url','varchar(100)') AS URL,

    Greets

    Flo

  • April 24, 2009 at 2:15 pm

    #983829

    Thanks Florian. That solved it. Have a great weekend:cool:

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply