October 11, 2012 at 4:52 am
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
October 11, 2012 at 5:08 am
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
October 11, 2012 at 5:11 am
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.
October 11, 2012 at 6:15 am
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