SQL Server Performance
  1. arkiboys Member

    I am working on an xml file which has similar contents as below.
    Note that the issuer for each profile may be different.
    I can get all the values out of the xml except the issuer because the values sections can vary as you see below.
    Any suggestions on how to modify my sql that I am using to get issuer data please?
    Note the sql shows how to get issuer when there are three values but I do not know how to modify it so that other issuers for other profiles are retrieved correctly.
    Any thoughts please?
    Thanks
    ------------xml sample
    <profilelist>
    <profile>
    <form>my name</form>
    <blog>sectionPoint</blog>
    <issuer>
    <value>x</value>
    <value>y</value>
    <value>z</value>
    </issuer>
    </profile>
    <profile>
    <form>galop</form>
    <blog>SierraPoint</blog>
    <issuer>
    <value>x</value>
    </issuer>
    </profile>
    <profile>
    <form>data</form>
    <blog>points</blog>
    <issuer>myIssuer</issuer>
    </profile>
    </profilelist>
    --sql I am using
    declare @XMLdoc xml
    select @XMLdoc = dataXML from tblImportXML
    select
    v.p.value('./form[1]','varchar(50)') AS form,
    v.p.value('./blog[1]','varchar(50)') AS blog,
    v.p.value('./Issuer[1]/value[1]','varchar(50)') AS IssuerValue1,
    v.p.value('./Issuer[1]/value[2]','varchar(50)') AS IssuerValue2,
    v.p.value('./Issuer[1]/value[3]','varchar(50)') AS IssuerValue3
    from @XMLdoc.nodes('/profilelist/profile')v (p)

Share This Page