0

I am a total noob with XQuery, but before at start digging deep into it, i'd like to ask some experts advice about whether i am looking at the correct direction.

I have XML in table that something looks like :

'<JOURNALEXT>
<JOURNAL journalno="1" journalpos="1" ledgercode="TD1">
</JOURNAL>
<JOURNAL journalno="1" journalpos="1" ledgercode="TD2">
</JOURNAL>
<JOURNAL journalno="1" journalpos="1" ledgercode="TD3">`enter code here`
</JOURNAL>
-----almost 50 such nodes
</JOURNALEXT>'

Now the ledger code attribute's value is there in some table. I have to filter all the nodes whose ledgercode value is not in the value that is there in table.

For example my ledger_code table has two entries TD1 & TD2

so I should get the resultant XML as 

<JOURNALEXT>

<JOURNAL journalno="1" journalpos="1" ledgercode="TD3">
</JOURNAL>
-----almost 50 such nodes
</JOURNALEXT>

I can delete nodes based on one attribute by using. 

declare @var_1 varchar(max) = 'TD1' 
        BEGIN TRANSACTION 
update [staging_data_load].[TBL_STG_RAWXML_STORE] WITH (rowlock) 
set XMLDATA.modify('delete /JOURNALEXT/JOURNAL[@ledgercode!= sql:variable("@var_1")]') 
where job_id=@job_Id

but my case is quite complex..i need to get multiple ledgercodes from table and make sure only those nodes having table consisting ledgercodes remain. Rest all gets deleted.

I am using MS SQL SERVER 2012 ...as database and trying to write an xquery.

Vikas Kumar
  • 87
  • 2
  • 18
  • There is absolutely no need to SHOUT AT US when asking your question. It's rude, rather annoying, and will not get you help any sooner. Neither will begging because you're in "dire straits" - if you need urgent help pay someone to help you. All questions here are equally important and "urgent", and it's rather impolite to be setting a timeline when you're asking people for their **free help** to solve **your problem**. It will help you get answers more quickly if you add tags that are applicable to your question; for instance, what DBMS are you using to execute the SQL? – Ken White May 23 '14 at 12:42
  • @Ken White - I am sorry for my lack of etiquettes. This is my first post, unknowingly may be I couldn't follow norm. Sorry Sir... – Vikas Kumar May 23 '14 at 13:43
  • possible duplicate of [Deleting Multiple Nodes in Single XQuery for SQL Server](http://stackoverflow.com/questions/1020305/deleting-multiple-nodes-in-single-xquery-for-sql-server) – Paul Sweatte Jun 24 '14 at 16:15

0 Answers0