February 17, 2016 at 12:58 am
Hi
I want to get "Volvo12" OR "Volvo43" From this field.
<main>
<purch id="14" ...="0">
<item id="449" ... />
<item id="450" ... />
<... >
</purch>
<invo>
<Volvo12 vid="24549" />
<Volvo43 vid="88357" />
<...>
</invo>
</main>
which Tsql Can I Use ?
February 17, 2016 at 2:13 am
Next time please post a valid XML fragment so that I don't need to guess how the XMML looks.
I think the below does what you need. You might have to change the WHERE clause - your requirements are not very clear based on the explanation and sample data.
DECLARE @x xml = '<main>
<purch id="14" a="0">
<item id="449" a="0" />
<item id="450" a="0" />
<Something />
</purch>
<invo>
<Volvo12 vid="24549" />
<Volvo43 vid="88357" />
<Something />
</invo>
</main>';
SELECT n.node.value('fn:local-name(.)', 'nvarchar(50)') as NodeName
FROM @x.nodes('/main/invo/*') AS n(node)
WHERE n.node.value('fn:local-name(.)', 'nvarchar(50)') LIKE 'Volvo%';
February 17, 2016 at 4:16 am
The result Exactly is True
Thank you
😛
February 17, 2016 at 7:11 am
DECLARE @x xml = '<main>
<purch id="14" a="0">
<item id="449" a="0" />
<item id="450" a="0" />
<Something />
</purch>
<invo>
<Volvo12 vid="24549" />
<Volvo43 vid="88357" />
<Something />
</invo>
</main>';
But If @x will be a field in a table and I want vid Value beside it ,
Volvo12 24549
Volvo43 88357
I create an error :
SELECT id,[Data]
,n.node.value('fn:local-name(.)', 'nvarchar(50)') vv
,[Data].value('(main/inv/'+Convert(varchar(50),n.node.value('fn:local-name(.)', 'nvarchar(50)'))+'/@voucher-articel-id)[1]', 'bigint') As VoucherArticelId
FROM [dbo].[TBL_Receipt]
CROSS APPLY [Data].nodes('/main/inv/*') AS n(node)
Where id=1
Error :
The argument 1 of the XML data type method "value" must be a string literal.
February 17, 2016 at 7:32 am
It would have been a lot easier if you had posted the full question immediately.
Below is the code to get both the node name and the "vid" attribute value for the string.
If you need help extracting it from a table, post CREATE TABLE and INSERT statements, plus expected results.
DECLARE @x xml = '<main>
<purch id="14" a="0">
<item id="449" a="0" />
<item id="450" a="0" />
<Something />
</purch>
<invo>
<Volvo12 vid="24549" />
<Volvo43 vid="88357" />
<Something />
</invo>
</main>';
SELECT n.node.value('fn:local-name(.)', 'nvarchar(50)') as NodeName,
n.node.value('./@vid[1]', 'nvarchar(10)') AS VidValue
FROM @x.nodes('/main/invo/*') AS n(node)
WHERE n.node.value('fn:local-name(.)', 'nvarchar(50)') LIKE 'Volvo%';
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply