hopefully last XML question, deleting sections

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

hopefully last XML question, deleting sections

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-06-27 : 18:03:41

If this is my XML

<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd">
<Data>
<Document type="DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT" created="2014-06-10T00:31:22" schemaVersion="1.0">
<NewAdvices>
<DDICAdvice>
<SeqNo>2014060903A897614068</SeqNo>
<NoOfAdvForClaim>1</NoOfAdvForClaim>
<TotalAmount>22.20</TotalAmount>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-04-01</DateOfDirectDebit>
<Amount>22.20</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<DDICAdvice>
<SeqNo>2014060903A897614069</SeqNo>
<NoOfAdvForClaim>1</NoOfAdvForClaim>
<TotalAmount>22.20</TotalAmount>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
<Amount>22.20</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<DDICAdvice>
<SeqNo>2014060903A897614070</SeqNo>
<NoOfAdvForClaim>1</NoOfAdvForClaim>
<TotalAmount>22.20</TotalAmount>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-06-02</DateOfDirectDebit>
<Amount>22.20</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<TotalNumberOfNewAdvices>3</TotalNumberOfNewAdvices>
<TotalValueOfDebits>66.60</TotalValueOfDebits>
<DateOfDebit>2014-06-27</DateOfDebit>
</NewAdvices>
</Document>
</Data>
</VocaDocument>

How can I delete sections out by the seqNo
ie if I parm in seqno 2014060903A897614068
I get

<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd">
<Data>
<Document type="DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT" created="2014-06-10T00:31:22" schemaVersion="1.0">
<NewAdvices>
<DDICAdvice>
<SeqNo>2014060903A897614068</SeqNo>
<NoOfAdvForClaim>1</NoOfAdvForClaim>
<TotalAmount>22.20</TotalAmount>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-04-01</DateOfDirectDebit>
<Amount>22.20</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<TotalNumberOfNewAdvices>3</TotalNumberOfNewAdvices>
<TotalValueOfDebits>66.60</TotalValueOfDebits>
<DateOfDebit>2014-06-27</DateOfDebit>
</NewAdvices>
</Document>
</Data>
</VocaDocument>

if I parm in 2014060903A897614069
I get
<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd">
<Data>
<Document type="DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT" created="2014-06-10T00:31:22" schemaVersion="1.0">
<NewAdvices>
<DDICAdvice>
<SeqNo>2014060903A897614069</SeqNo>
<NoOfAdvForClaim>1</NoOfAdvForClaim>
<TotalAmount>22.20</TotalAmount>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
<Amount>22.20</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<TotalNumberOfNewAdvices>3</TotalNumberOfNewAdvices>
<TotalValueOfDebits>66.60</TotalValueOfDebits>
<DateOfDebit>2014-06-27</DateOfDebit>
</NewAdvices>
</Document>
</Data>
</VocaDocument>

etc

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-06-29 : 07:55:17

I have managed to get so far, but just cannot crack this last bit. Im my temp table there are three records, Usinf the identity colum, I want to delete the DDICAdvice section that is not = to the MyID number, ie second record MYID = 2 so delect all DDICAdvice where position is not = 2

I come up with this but get a syntax error

DECLARE @MYID vARCHAR(2) , @CURRENTXML XML
DECLARE UPDATE_TABLE_CURSOR CURSOR FOR
SELECT cast(MYID AS varchar(2)), @CURRENTXML FROM #UserDDIC
OPEN UPDATE_TABLE_CURSOR
FETCH NEXT FROM UPDATE_TABLE_CURSOR INTO @MYID, @CURRENTXML
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @UPDATESTRING AS VARCHAR(100)
SET @UPDATESTRING = @UPDATESTRING
SET @x.modify (' delete (/VocaDocument/Data/Document/NewAdvices/DDICAdvice[position()!="{sql:variable("@MYID")}"])')

FETCH NEXT FROM UPDATE_TABLE_CURSOR INTO @MYID, @CURRENTXML
END
CLOSE UPDATE_TABLE_CURSOR
DEALLOCATE UPDATE_TABLE_CURSOR

Syntax error - XQuery [modify()]: Syntax error near '@', expected ']'.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-30 : 07:44:53

What's in table #UserDDIC?

Also, I don't think you need a cursor for this, but let's see what's in the temp table first

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-30 : 12:12:02

Built on top of my previous answer.

DECLARE	@Data XML = '	<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd">
				<Data>
					<Document type="DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT" created="2014-06-10T00:31:22" schemaVersion="1.0">
						<NewAdvices>
							<DDICAdvice>
								<SeqNo>2014060903A897614068</SeqNo>
								<NoOfAdvForClaim>1</NoOfAdvForClaim>
								<TotalAmount>22.20</TotalAmount>
								<DDCollections>
									<DDCollection>
										<DateOfDirectDebit>2014-04-01</DateOfDirectDebit>
										<Amount>22.20</Amount>
									</DDCollection>
								</DDCollections>
							</DDICAdvice>
							<DDICAdvice>
								<SeqNo>2014060903A897614069</SeqNo>
								<NoOfAdvForClaim>1</NoOfAdvForClaim>
								<TotalAmount>22.20</TotalAmount>
								<DDCollections>
									<DDCollection>
										<DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
										<Amount>22.20</Amount>
									</DDCollection>
								</DDCollections>
							</DDICAdvice>
							<DDICAdvice>
								<SeqNo>2014060903A897614070</SeqNo>
								<NoOfAdvForClaim>1</NoOfAdvForClaim>
								<TotalAmount>22.20</TotalAmount>
								<DDCollections>
									<DDCollection>
										<DateOfDirectDebit>2014-06-02</DateOfDirectDebit>
										<Amount>22.20</Amount>
									</DDCollection>
								</DDCollections>
							</DDICAdvice>
							<TotalNumberOfNewAdvices>3</TotalNumberOfNewAdvices>
							<TotalValueOfDebits>66.60</TotalValueOfDebits>
							<DateOfDebit>2014-06-27</DateOfDebit>
						</NewAdvices>
					</Document>
				</Data>
			</VocaDocument>';

DECLARE	@SeqNo VARCHAR(25) = '2014060903A897614069';

-- SwePeso
SELECT	@Data.query('
			<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd">
				<Data>
					<Document type="{VocaDocument/Data/Document/@type}" created="{VocaDocument/Data/Document/@created}" schemaVersion="{VocaDocument/Data/Document/@schemaVersion}">
						<NewAdvices>
							<DDICAdvice>
								{VocaDocument/Data/Document/NewAdvices/DDICAdvice[SeqNo=sql:variable("@SeqNo")]}
							</DDICAdvice>
							{VocaDocument/Data/Document/NewAdvices/TotalNumberOfNewAdvices}
							{VocaDocument/Data/Document/NewAdvices/TotalValueOfDebits}
							{VocaDocument/Data/Document/NewAdvices/DateOfDebit}
						</NewAdvices>
					</Document>
				</Data>
			</VocaDocument>
			');

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources