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.
Author |
Topic |
Pete_N
Posting Yak Master
181 Posts |
Posted - 2014-06-26 : 15:33:00
|
<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd"> <Data> <Document type="DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT" > <NewAdvices> <DDICAdvice> <SeqNo>123456</SeqNo> <SUReference>000001</SUReference> <ReasonCode>2</ReasonCode> <NoOfAdvForClaim>1</NoOfAdvForClaim> <TotalAmount>40.00</TotalAmount> <DDCollections> <DDCollection> <DateOfDirectDebit>2014-04-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> <DDCollection> <DateOfDirectDebit>2014-05-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> </DDCollections> </DDICAdvice> <DDICAdvice> <SeqNo>75758</SeqNo> <SUReference>32544</SUReference> <ReasonCode>2</ReasonCode> <NoOfAdvForClaim>1</NoOfAdvForClaim> <TotalAmount>20.00</TotalAmount> <DDCollections> <DDCollection> <DateOfDirectDebit>2014-05-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> </DDCollections> </DDICAdvice> <TotalNumberOfNewAdvices>2</TotalNumberOfNewAdvices> <TotalValueOfDebits>60.00</TotalValueOfDebits> <DateOfDebit>2014-05-30</DateOfDebit> </NewAdvices> </Document> </Data> </VocaDocument>
I need to get the DateOfDirectDebit and Amount for each SeqNo to load into a temp table. This is section of a larger report and Im trying to break it up to send out reports to different customers
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-26 : 16:29:56
|
Here is an example that works. I would recommend replacing the '//' that I have in the FROM clause with the exact path. DECLARE @x 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"> <NewAdvices> <DDICAdvice> <SeqNo>123456</SeqNo> <SUReference>000001</SUReference> <ReasonCode>2</ReasonCode> <NoOfAdvForClaim>1</NoOfAdvForClaim> <TotalAmount>40.00</TotalAmount> <DDCollections> <DDCollection> <DateOfDirectDebit>2014-04-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> <DDCollection> <DateOfDirectDebit>2014-05-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> </DDCollections> </DDICAdvice> <DDICAdvice> <SeqNo>75758</SeqNo> <SUReference>32544</SUReference> <ReasonCode>2</ReasonCode> <NoOfAdvForClaim>1</NoOfAdvForClaim> <TotalAmount>20.00</TotalAmount> <DDCollections> <DDCollection> <DateOfDirectDebit>2014-05-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> </DDCollections> </DDICAdvice> <TotalNumberOfNewAdvices>2</TotalNumberOfNewAdvices> <TotalValueOfDebits>60.00</TotalValueOfDebits> <DateOfDebit>2014-05-30</DateOfDebit> </NewAdvices> </Document> </Data> </VocaDocument>';
SELECT c1.value('SeqNo[1]','varchar(32)') AS SeqNo, c2.value('DateOfDirectDebit[1]','varchar(32)') AS DateOfDirectDebit, c2.value('Amount[1]','varchar(32)') AS Amount INTO #YourTempTable FROM @x.nodes('//DDICAdvice') T1(c1) CROSS APPLY c1.nodes('//DDCollection') T2(c2); |
 |
|
Pete_N
Posting Yak Master
181 Posts |
Posted - 2014-06-26 : 18:55:48
|
HI,
I have tried to incorporate that in my code but the results are incorrect. I have imported the xml into a temptable as I amend some details, can you help
CREATE TABLE #WORKINGXML ( XMLTEXT XML )
SELECT a.c.value('SeqNo[1]','varchar(32)') AS SeqNo, t2.c2.value('DateOfDirectDebit[1]','varchar(32)') AS DateOfDirectDebit, t2.c2.value('Amount[1]','varchar(32)') AS Amount FROM ( SELECT XMLTEXT AS Xmlreport FROM #WORKINGXML) d CROSS APPLY Xmlreport.nodes('/VocaDocument/Data/Document/NewAdvices/DDICAdvice') AS a(c) CROSS APPLY Xmlreport.nodes('/VocaDocument/Data/Document/NewAdvices/DDICAdvice/DDCollections/DDCollection') T2(c2); |
 |
|
Pete_N
Posting Yak Master
181 Posts |
Posted - 2014-06-27 : 07:46:21
|
I have tried the following
<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-05-10T00:24:40" schemaVersion="1.0"> <CompanyName>Bacs Payment Schemes Limited</CompanyName> <NewAdvices> <DDICAdvice> <SeqNo>2014050903A889958164</SeqNo> <DDCollections> <DDCollection> <DateOfDirectDebit>2014-04-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> <DDCollection> <DateOfDirectDebit>2014-05-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> </DDCollections> </DDICAdvice> <DDICAdvice> <SeqNo>2014050903A889958170</SeqNo> <DDCollections> <DDCollection> <DateOfDirectDebit>2014-05-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> </DDCollections> </DDICAdvice> <TotalNumberOfNewAdvices>2</TotalNumberOfNewAdvices> <TotalValueOfDebits>60.00</TotalValueOfDebits> <DateOfDebit>2014-05-30</DateOfDebit> </NewAdvices> </Document> </Data> </VocaDocument>
CREATE TABLE #WORKINGXML ( XMLTEXT XML )
/* Load XML into temporary table */ INSERT INTO #WORKINGXML SELECT XMLTEXT FROM @NEWDDICtable /* REMOVE SIGNATURE NODE */ Set @SQL = 'UPDATE #WORKINGXML SET XMLText.modify(''delete /VocaDocument/Signature'')' exec sp_executesql @SQL Set @SQL = 'UPDATE #WORKINGXML SET XMLText.modify(''delete /VocaDocument/SignatureMethod'')' exec sp_executesql @SQL IF @@ERROR <> 0
SELECT a.c.value('SeqNo[1]','varchar(32)') AS SeqNo, t2.c2.value('DateOfDirectDebit[1]','varchar(32)') AS DateOfDirectDebit, t2.c2.value('Amount[1]','varchar(32)') AS Amount FROM ( SELECT XMLTEXT AS Xmlreport FROM #WORKINGXML) d CROSS APPLY Xmlreport.nodes('/VocaDocument/Data/Document/NewAdvices/DDICAdvice') AS a(c) CROSS APPLY Xmlreport.nodes('/VocaDocument/Data/Document/NewAdvices/DDICAdvice/DDCollections/DDCollection') T2(c2) WHERE a.c.value('SeqNo[1]', 'varchar(32)') = '2014050903A889958164'
But I get the following results
SeqNo DateOfDirectDebit Amount 2014050903A889958164 2014-04-01 20.00 2014050903A889958164 2014-05-01 20.00 2014050903A889958164 2014-05-01 20.00
I should only get two records returned
|
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-27 : 08:49:41
|
In the second cross apply don't go back to the root node, change it to... CROSS APPLY c.nodes('DDCollections/DDCollection') T2 ( c2 ) ... |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-27 : 09:10:13
|
[code]DECLARE @Sample TABLE ( RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Data XML NOT NULL );
INSERT @Sample ( Data ) VALUES (' <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-05-10T00:24:40" schemaVersion="1.0"> <CompanyName>Bacs Payment Schemes Limited</CompanyName> <NewAdvices> <DDICAdvice> <SeqNo>2014050903A889958164</SeqNo> <DDCollections> <DDCollection> <DateOfDirectDebit>2014-04-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> <DDCollection> <DateOfDirectDebit>2014-05-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> </DDCollections> </DDICAdvice> <DDICAdvice> <SeqNo>2014050903A889958170</SeqNo> <DDCollections> <DDCollection> <DateOfDirectDebit>2014-05-01</DateOfDirectDebit> <Amount>20.00</Amount> </DDCollection> </DDCollections> </DDICAdvice> <TotalNumberOfNewAdvices>2</TotalNumberOfNewAdvices> <TotalValueOfDebits>60.00</TotalValueOfDebits> <DateOfDebit>2014-05-30</DateOfDebit> </NewAdvices> </Document> </Data> </VocaDocument>');
-- SwePeso DECLARE @SeqNo VARCHAR(32) = '2014050903A889958164';
SELECT a.n.value('SeqNo[1]', 'VARCHAR(32)') AS SeqNo, b.n.value('DateOfDirectDebit[1]', 'DATETIME') AS DateOfDirectDebit, b.n.value('Amount[1]', 'MONEY') AS Amount FROM @Sample AS s CROSS APPLY Data.nodes('(VocaDocument/Data/Document/NewAdvices/DDICAdvice[SeqNo=sql:variable("@SeqNo")])') AS a(n) CROSS APPLY a.n.nodes('(DDCollections/DDCollection)') AS b(n);
SET @SeqNo = '2014050903A889958170';
SELECT a.n.value('SeqNo[1]', 'VARCHAR(32)') AS SeqNo, b.n.value('DateOfDirectDebit[1]', 'DATETIME') AS DateOfDirectDebit, b.n.value('Amount[1]', 'MONEY') AS Amount FROM @Sample AS s CROSS APPLY Data.nodes('(VocaDocument/Data/Document/NewAdvices/DDICAdvice[SeqNo=sql:variable("@SeqNo")])') AS a(n) CROSS APPLY a.n.nodes('(DDCollections/DDCollection)') AS b(n);[/code]
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-27 : 10:43:14
|
quote: SELECT a.n.value('SeqNo[1]', 'VARCHAR(32)') AS SeqNo, b.n.value('DateOfDirectDebit[1]', 'DATETIME') AS DateOfDirectDebit, b.n.value('Amount[1]', 'MONEY') AS Amount FROM @Sample AS s CROSS APPLY Data.nodes('(VocaDocument/Data/Document/NewAdvices/DDICAdvice[SeqNo=sql:variable("@SeqNo")])') AS a(n) CROSS APPLY a.n.nodes('(DDCollections/DDCollection)') AS b(n);
https://www.youtube.com/watch?v=218iXiKhKlg
|
 |
|
|
|
|
|
|