Post reply

Extracting XML

  • December 4, 2014 at 5:50 pm

    #313640

    Hi all. I am trying to extract what I believe is called a Node from a column in a table that contains XML. What's the best way to do this? It is pretty straightforward since I'm not even looking to include a WHERE clause. I've tried searching for ways to do this but I'm a beginner and this has me stumped.

    What I have so far is:

    SELECT CCH.OrderID, CCH.CCXML.query('/cc/auth')

    FROM tblCCH AS CCH

    Thanks in advance for any help on this.

    Dan

  • December 4, 2014 at 6:08 pm

    #1762846

    post a few rows of the table you are querying along with the results of your query

    Gerald Britton, Pluralsight courses

  • December 4, 2014 at 7:24 pm

    #1762847

    I can't pull anything out of the table at all. But here is some sample data. Note that I can't see what's in the XML column unless do a general SELECT * FROM tblCCHistory and export it to Excel. But here goes!

    The columns are PKID, OrderID, and CCXML. I've listed out the XML data, but normally I can't see it unless I ship it out to Excel.

    1,31677, <?xml version="1.0"?> <CC> <Type>4</Type> <Name><![CDATA[Lisa M. xxxxx]]></Name> <Amt>51.86</Amt> <Auth>074415</Auth> </CC>

    2,31678,<?xml version="1.0"?> <CC> <Type>4</Type> <Name><![CDATA[John R. XXXX]]></Name> <Amt>25.00</Amt> <Auth>074415</Auth> </CC>

    Is this what you meant? I'm looking to pull out the order ID and the data in the Auth node.

    Thanks!

    Dan

  • December 4, 2014 at 9:05 pm

    #1762856

    -- Sample data matching what you have

    DECLARE @tblCCH TABLE

    (PKID int primary key,

    OrderID int not null,

    CCXML xml not null

    );

    INSERT @tblCCH

    SELECT 1,31677,'<?xml version="1.0"?> <CC> <Type>4</Type> <Name><![CDATA[Lisa M. xxxxx]]></Name> <Amt>51.86</Amt> <Auth>074415</Auth> </CC>'

    UNION ALL

    SELECT 2,31678,'<?xml version="1.0"?> <CC> <Type>4</Type> <Name><![CDATA[John R. XXXX]]></Name> <Amt>25.00</Amt> <Auth>074416</Auth> </CC> '

    -- sample data review

    SELECT * FROM @tblCCH

    -- extracting text from an XML Value

    SELECT PKID,

    OrderID,

    CCXML.value('(CC/Auth/text())[1]', 'varchar(10)') AS Auth

    FROM @tblCCH;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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