Post reply

Shredding XML

  • January 11, 2016 at 8:34 pm

    #323470

    Hello,

    I'm new to XML and SQL Server. I am trying to extract the CustomerID field from the following stored as XML in a SQL Server column.

    XML data:

    <ns0:Contact xmlns:ns0="http://schemas.fanta.com/CRM/Contact/2016-01-02">

    <AccountId />

    <CustomerId>9999999</CustomerId>

    </ns0:Contact>

    Query I'm using:

    select dummyxml.value('(/CustomerId)[1]', 'nvarchar(max)') from [dbo].[DummyTable]

    I am receiving NULL values as result of executing this query. Please advise.

    Thank you,

    Sam

  • January 11, 2016 at 9:42 pm

    #1851219

    Try:

    select dummyxml.value('(/*:Contact/*:CustomerId/text())[1]', 'nvarchar(max)')

    from [dbo].[DummyTable]

    "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

  • January 12, 2016 at 9:54 am

    #1851335

    Thank you Sir. That worked. Will study up on XML.

  • January 12, 2016 at 9:55 am

    #1851336

    Any simple answer to why I was receiving null from the query I was using?

  • January 12, 2016 at 10:11 am

    #1851348

    guru2007 (1/12/2016)

    Any simple answer to why I was receiving null from the query I was using?

    Missing root note reference and root note namespace

    😎

    Another way is to use XMLNAMESPACES

    DECLARE @TXML XML = '<ns0:Contact xmlns:ns0="http://schemas.fanta.com/CRM/Contact/2016-01-02">

    <AccountId />

    <CustomerId>9999999</CustomerId>

    </ns0:Contact>';

    ;WITH XMLNAMESPACES ('http://schemas.fanta.com/CRM/Contact/2016-01-02' AS XX)

    SELECT @TXML.value('(XX:Contact/CustomerId/text())[1]', 'nvarchar(max)');

  • January 12, 2016 at 12:24 pm

    #1851390

    guru2007 (1/12/2016)

    Any simple answer to why I was receiving null from the query I was using?

    Just adding to what Eirikur said - returning a NULL is the default behavior for when your there is no node which corresponds with your XPath expression.

    In addition to adding the root node (contact) I added "*:" which is the lazy man's way of saying "any namespace". Explicitly adding a namespace as Eirikur did is the better way to do it.

    A good place to learn about XML is W3Schools.com.

    "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 6 posts - 1 through 5 (of 5 total)

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