XML query help

Discussion in 'microsoft.public.sqlserver.programming' started by Q, Mar 7, 2005.

  1. Q

    Q Guest

    I got the following XML format, but I don't know how to pull data out of it.
    Expect results:

    CustomValue Value
    -----------------------------
    ApplicantFICOScore 804

    Thanks all for your help,
    Q.

    DECLARE @xml VARCHAR(8000), @idoc INT
    SET @xml =
    '<?xml version="1.0"?>
    <decisionresponse xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    decisionrequestid="12345">
    <testmode>false</testmode>
    <decision>EXPEDITE</decision>
    <titleordered>true</titleordered>
    <customvaluelist>
    <customvalue key="ApplicantFICOScore">804</customvalue>
    <customvalue key="ApplicantFICOException" />
    <customvalue key="ApplicantRiskFactor-1">10</customvalue>
    <customvalue key="ApplicantRiskFactor-1-Description">CREDIT
    LIMITS</customvalue>
    <customvalue key="ApplicantRiskFactor-2">05</customvalue>
    <customvalue key="ApplicantRiskFactor-2-Description">NUMBER OF ACCOUNTS
    WITH BALANCES</customvalue>
    <customvalue key="ApplicantRiskFactor-3" />
    <customvalue key="ApplicantRiskFactor-3-Description" />
    <customvalue key="ApplicantRiskFactor-4" />
    <customvalue key="ApplicantRiskFactor-4-Description" />
    <customvalue key="CoApplicantFICOScore">793</customvalue>
    <customvalue key="CoApplicantFICOException" />
    <customvalue key="CoApplicantRiskFactor-1">10</customvalue>
    <customvalue key="CoApplicantRiskFactor-1-Description">CREDIT
    LIMITS</customvalue>
    <customvalue key="CoApplicantRiskFactor-2">05</customvalue>
    <customvalue key="CoApplicantRiskFactor-2-Description">NUMBER OF ACCOUNTS
    WITH BALANCES</customvalue>
    <customvalue key="CoApplicantRiskFactor-3">09</customvalue>
    <customvalue key="CoApplicantRiskFactor-3-Description">NUMBER OF ACCOUNTS
    OPENED</customvalue>
    <customvalue key="CoApplicantRiskFactor-4" /> <customvalue
    key="CoApplicantRiskFactor-4-Description" />
    <customvalue key="LowFICOScore">793</customvalue>
    </customvaluelist>
    </decisionresponse>'


    EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
    SELECT *
    FROM OPENXML ( @idoc, '/decisionresponse/customvaluelist' )

    EXEC sp_xml_removedocument @idoc
     
    Q, Mar 7, 2005
    #1
    1. Advertisements

  2. Q

    Tom Moreau Guest

    Check out:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01c5.asp

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com
    ..
    I got the following XML format, but I don't know how to pull data out of it.
    Expect results:

    CustomValue Value
    -----------------------------
    ApplicantFICOScore 804

    Thanks all for your help,
    Q.

    DECLARE @xml VARCHAR(8000), @idoc INT
    SET @xml =
    '<?xml version="1.0"?>
    <decisionresponse xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    decisionrequestid="12345">
    <testmode>false</testmode>
    <decision>EXPEDITE</decision>
    <titleordered>true</titleordered>
    <customvaluelist>
    <customvalue key="ApplicantFICOScore">804</customvalue>
    <customvalue key="ApplicantFICOException" />
    <customvalue key="ApplicantRiskFactor-1">10</customvalue>
    <customvalue key="ApplicantRiskFactor-1-Description">CREDIT
    LIMITS</customvalue>
    <customvalue key="ApplicantRiskFactor-2">05</customvalue>
    <customvalue key="ApplicantRiskFactor-2-Description">NUMBER OF ACCOUNTS
    WITH BALANCES</customvalue>
    <customvalue key="ApplicantRiskFactor-3" />
    <customvalue key="ApplicantRiskFactor-3-Description" />
    <customvalue key="ApplicantRiskFactor-4" />
    <customvalue key="ApplicantRiskFactor-4-Description" />
    <customvalue key="CoApplicantFICOScore">793</customvalue>
    <customvalue key="CoApplicantFICOException" />
    <customvalue key="CoApplicantRiskFactor-1">10</customvalue>
    <customvalue key="CoApplicantRiskFactor-1-Description">CREDIT
    LIMITS</customvalue>
    <customvalue key="CoApplicantRiskFactor-2">05</customvalue>
    <customvalue key="CoApplicantRiskFactor-2-Description">NUMBER OF ACCOUNTS
    WITH BALANCES</customvalue>
    <customvalue key="CoApplicantRiskFactor-3">09</customvalue>
    <customvalue key="CoApplicantRiskFactor-3-Description">NUMBER OF ACCOUNTS
    OPENED</customvalue>
    <customvalue key="CoApplicantRiskFactor-4" /> <customvalue
    key="CoApplicantRiskFactor-4-Description" />
    <customvalue key="LowFICOScore">793</customvalue>
    </customvaluelist>
    </decisionresponse>'


    EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
    SELECT *
    FROM OPENXML ( @idoc, '/decisionresponse/customvaluelist' )

    EXEC sp_xml_removedocument @idoc
     
    Tom Moreau, Mar 7, 2005
    #2
    1. Advertisements

  3. Q

    Q Guest

    Please, I still need more help.

    No matter what I tried, I got:


    CustomerValue
    --------------
    ApplicantFICOScore

    CustomerValue Value
    -------------- -----
    ApplicantFICOScore 804

    The following is the statement that I tried:
    DECLARE @idoc int
    DECLARE @xml varchar(8000)
    SET @xml =
    '<?xml version="1.0"?>
    <decisionresponse xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    decisionrequestid="12345">
    <testmode>false</testmode>
    <decision>EXPEDITE</decision>
    <titleordered>true</titleordered>
    <customvaluelist>
    <customvalue key="ApplicantFICOScore">804</customvalue>
    <customvalue key="ApplicantFICOException" />
    <customvalue key="ApplicantRiskFactor-1">10</customvalue>
    <customvalue key="ApplicantRiskFactor-1-Description">CREDIT
    LIMITS</customvalue>
    <customvalue key="ApplicantRiskFactor-2">05</customvalue>
    <customvalue key="ApplicantRiskFactor-2-Description">NUMBER OF ACCOUNTS
    WITH BALANCES</customvalue>
    <customvalue key="ApplicantRiskFactor-3" />
    <customvalue key="ApplicantRiskFactor-3-Description" />
    <customvalue key="ApplicantRiskFactor-4" />
    <customvalue key="ApplicantRiskFactor-4-Description" />
    <customvalue key="CoApplicantFICOScore">793</customvalue>
    <customvalue key="CoApplicantFICOException" />
    <customvalue key="CoApplicantRiskFactor-1">10</customvalue>
    <customvalue key="CoApplicantRiskFactor-1-Description">CREDIT
    LIMITS</customvalue>
    <customvalue key="CoApplicantRiskFactor-2">05</customvalue>
    <customvalue key="CoApplicantRiskFactor-2-Description">NUMBER OF ACCOUNTS
    WITH BALANCES</customvalue>
    <customvalue key="CoApplicantRiskFactor-3">09</customvalue>
    <customvalue key="CoApplicantRiskFactor-3-Description">NUMBER OF ACCOUNTS
    OPENED</customvalue>
    <customvalue key="CoApplicantRiskFactor-4" /> <customvalue
    key="CoApplicantRiskFactor-4-Description" />
    <customvalue key="LowFICOScore">793</customvalue>
    </customvaluelist>
    </decisionresponse>'

    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

    SELECT *
    FROM OPENXML ( @idoc, '/decisionresponse/customvaluelist/customvalue',1 )
    with
    ( [CustomerValue] varchar(100) '@key'
    -- ,[Value] varchar(100) '@text'
    )

    -- Remove the internal representation.
    exec sp_xml_removedocument @idoc
     
    Q, Mar 8, 2005
    #3
  4. Q

    Tom Moreau Guest

    I just cut and pasted your code and got:


    CustomerValue
    ------------------------------------------------
    ApplicantFICOScore
    ApplicantFICOException
    ApplicantRiskFactor-1
    ApplicantRiskFactor-1-Description
    ApplicantRiskFactor-2
    ApplicantRiskFactor-2-Description
    ApplicantRiskFactor-3
    ApplicantRiskFactor-3-Description
    ApplicantRiskFactor-4
    ApplicantRiskFactor-4-Description
    CoApplicantFICOScore
    CoApplicantFICOException
    CoApplicantRiskFactor-1
    CoApplicantRiskFactor-1-Description
    CoApplicantRiskFactor-2
    CoApplicantRiskFactor-2-Description
    CoApplicantRiskFactor-3
    CoApplicantRiskFactor-3-Description
    CoApplicantRiskFactor-4
    CoApplicantRiskFactor-4-Description
    LowFICOScore


    What are you expecting?


    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com
    ..
    Please, I still need more help.

    No matter what I tried, I got:


    CustomerValue
    --------------
    ApplicantFICOScore

    CustomerValue Value
    -------------- -----
    ApplicantFICOScore 804

    The following is the statement that I tried:
    DECLARE @idoc int
    DECLARE @xml varchar(8000)
    SET @xml =
    '<?xml version="1.0"?>
    <decisionresponse xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    decisionrequestid="12345">
    <testmode>false</testmode>
    <decision>EXPEDITE</decision>
    <titleordered>true</titleordered>
    <customvaluelist>
    <customvalue key="ApplicantFICOScore">804</customvalue>
    <customvalue key="ApplicantFICOException" />
    <customvalue key="ApplicantRiskFactor-1">10</customvalue>
    <customvalue key="ApplicantRiskFactor-1-Description">CREDIT
    LIMITS</customvalue>
    <customvalue key="ApplicantRiskFactor-2">05</customvalue>
    <customvalue key="ApplicantRiskFactor-2-Description">NUMBER OF ACCOUNTS
    WITH BALANCES</customvalue>
    <customvalue key="ApplicantRiskFactor-3" />
    <customvalue key="ApplicantRiskFactor-3-Description" />
    <customvalue key="ApplicantRiskFactor-4" />
    <customvalue key="ApplicantRiskFactor-4-Description" />
    <customvalue key="CoApplicantFICOScore">793</customvalue>
    <customvalue key="CoApplicantFICOException" />
    <customvalue key="CoApplicantRiskFactor-1">10</customvalue>
    <customvalue key="CoApplicantRiskFactor-1-Description">CREDIT
    LIMITS</customvalue>
    <customvalue key="CoApplicantRiskFactor-2">05</customvalue>
    <customvalue key="CoApplicantRiskFactor-2-Description">NUMBER OF ACCOUNTS
    WITH BALANCES</customvalue>
    <customvalue key="CoApplicantRiskFactor-3">09</customvalue>
    <customvalue key="CoApplicantRiskFactor-3-Description">NUMBER OF ACCOUNTS
    OPENED</customvalue>
    <customvalue key="CoApplicantRiskFactor-4" /> <customvalue
    key="CoApplicantRiskFactor-4-Description" />
    <customvalue key="LowFICOScore">793</customvalue>
    </customvaluelist>
    </decisionresponse>'

    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

    SELECT *
    FROM OPENXML ( @idoc, '/decisionresponse/customvaluelist/customvalue',1 )
    with
    ( [CustomerValue] varchar(100) '@key'
    -- ,[Value] varchar(100) '@text'
    )

    -- Remove the internal representation.
    exec sp_xml_removedocument @idoc
     
    Tom Moreau, Mar 8, 2005
    #4
  5. Q

    Q Guest

    Sorry, I was expecting :
    CustomerValue Value
    -------------- -----
    ApplicantFICOScore 804

    Thanks for your help.
    Q

     
    Q, Mar 8, 2005
    #5
  6. Q

    Tom Moreau Guest

    This give me that row plus all the others:

    SELECT *
    FROM OPENXML ( @idoc, '//customvalue',2 )
    with
    ( [CustomerValue] varchar(100) '@key'
    ,[Value] varchar(100) '.')


    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com
    ..
    Sorry, I was expecting :
    CustomerValue Value
    -------------- -----
    ApplicantFICOScore 804

    Thanks for your help.
    Q

     
    Tom Moreau, Mar 8, 2005
    #6
  7. Q

    Q Guest

    This is perfect for me, thanks.
    Q

     
    Q, Mar 8, 2005
    #7
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.