XML Tag reading

  • I am reading below XML through T-SQL query, please see below and suggest me the solution

    DECLARE @xmlAttributeRelation XML

    SET @xmlAttributeRelation='<Entity>

    <Entity>1</Entity>

    <Entity>2</Entity>

    </Entity>

    SELECT ISNULL(NULLIF(a.b.query('Entity').value('.', '[hf_id]'),''),0) AS Entity

    FROM @xmlAttributeRelation.nodes('/attributeRelationRequest/Entity') a(b)

    Result is:

    Entity

    --------

    12

    Expected Result is:

    Entity

    --------

    1

    2

    Appriciate your advise

  • venkat.2829 (10/11/2012)


    I am reading below XML through T-SQL query, please see below and suggest me the solution

    DECLARE @xmlAttributeRelation XML

    SET @xmlAttributeRelation='<Entity>

    <Entity>1</Entity>

    <Entity>2</Entity>

    </Entity>

    SELECT ISNULL(NULLIF(a.b.query('Entity').value('.', '[hf_id]'),''),0) AS Entity

    FROM @xmlAttributeRelation.nodes('/attributeRelationRequest/Entity') a(b)

    Result is:

    Entity

    --------

    12

    Expected Result is:

    Entity

    --------

    1

    2

    Appriciate your advise

    You could try the following (I edited the XML as I don't think it was valid, you can edit the query below to match your XML)...

    DECLARE @xmlAttributeRelation XML

    SET @xmlAttributeRelation='<attributeRelationRequest>

    <Entity>1</Entity>

    <Entity>2</Entity>

    </attributeRelationRequest>'

    ;WITH cte as

    (

    SELECT

    a.b.query('.') as Entity

    FROM @xmlAttributeRelation.nodes('/attributeRelationRequest') as a(b)

    )

    SELECT c.d.query('.').value('.', 'INT') ItemID

    FROM

    cte

    CROSS APPLY Entity.nodes('/attributeRelationRequest/Entity') as c(d)

    Cheers

    />L

    -----------------
    ... Then again, I could be totally wrong! Check the answer.
    Check out posting guidelines here for faster more precise answers[/url].

    I believe in Codd
    ... and Thinknook is my Chamber of Understanding

  • DECLARE @xmlAttributeRelation XML

    SET @xmlAttributeRelation='<Entity>

    <Entity>1</Entity>

    <Entity>2</Entity>

    </Entity>

    '

    SELECT Result = ent.val.value('.', 'int')

    FROM @xmlAttributeRelation.nodes('/Entity/Entity') ent(val)

    Result

    -----------

    1

    2

    (2 row(s) affected)

    It would be better to name the outer tag differently, e.g. "ArrayOfEntity" to avoid confusion.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • Thank you very much, it is working.

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

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