September 19, 2014 at 8:32 am
Hi ,
I am using following code to read xml input to table . I am not sure what I am doing wrong, I have no error and no output 🙁
Also can I use this select to dump data in a temp table ?
DECLARE @testXml XML;
select @testXml='<invoices>
<InvoiceNo>13537010*1</InvoiceNo>
<psship>value_psship1</psship>
<InvoiceNo>13537010*3</InvoiceNo>
<psship>value_psship2</psship>
<InvoiceNo>13537010*4</InvoiceNo>
<psship>value_psship3</psship>
<InvoiceNo>13537010*5</InvoiceNo>
<psship>value_psship4</psship>
<InvoiceNo>13537010*7</InvoiceNo>
<psship>value_psship5</psship>
</invoices>';
SELECT
Tab.Col.value('@Invoiceno','varchar(20)') AS InvoiceNo,
Tab.Col.value('@pssShip','varchar(20)') AS Shipvia
FROM @testXml.nodes('/invoices') Tab(Col)
Following is returning 5 rows but with NULL not real data
DECLARE @testXml XML;
SET @testXml='<InvoiceNo>13537010*1
<pShip>value_pShip1</pShip>
</InvoiceNo>
<InvoiceNo>13537010*3
<pShip>value_pShip2</pShip>
</InvoiceNo>
<InvoiceNo>13537010*4
<pShip>value_pShip3</pShip>
</InvoiceNo>
<InvoiceNo>13537010*5
<pShip>value_pShip4</pShip>
</InvoiceNo>
<InvoiceNo>13537010*7
<pShip>value_pShip5</pShip>
</InvoiceNo>';
SELECT
Tab.Col.value('@InvoiceNo','varchar(20)') AS InvoiceNo,
Tab.Col.value('@psShipVia','varchar(20)') AS PShip
FROM @testXml.nodes('/InvoiceNo') Tab(Col)
September 19, 2014 at 9:32 am
There's no attribute called InvoiceNo in your XML. That is, there needs to be something like this:
<Invoice InvoiceNo="12345" />
Gerald Britton, Pluralsight courses
September 19, 2014 at 10:27 am
thank you britton.
Following worked. I was not aware that XML column becomes case sensitive while selecting.
DECLARE @testXml XML;
set @testXml=
'<invoices>
<InvoiceNo>13537010*1</InvoiceNo>
<psship>value_psship1</psship>
</invoices>
<invoices>
<InvoiceNo>13537010*3</InvoiceNo>
<psship>value_psship2</psship>
</invoices>
';
SELECT
Tab.Col.value('InvoiceNo[1]','varchar (20)') AS InvoiceNo,
Tab.Col.value('psship[1]','varchar (20)') AS PShip
FROM @testXml.nodes('//invoices') Tab(Col)
September 19, 2014 at 10:37 am
Quick thought and as g.britton pointed out, the reference has to be exact, try this out for a size
😎
DECLARE @testXml XML;
select @testXml='<invoices>
<InvoiceNo>13537010*1</InvoiceNo>
<psship>value_psship1</psship>
<InvoiceNo>13537010*3</InvoiceNo>
<psship>value_psship2</psship>
<InvoiceNo>13537010*4</InvoiceNo>
<psship>value_psship3</psship>
<InvoiceNo>13537010*5</InvoiceNo>
<psship>value_psship4</psship>
<InvoiceNo>13537010*7</InvoiceNo>
<psship>value_psship5</psship>
</invoices>';
SELECT
INVOICE.DATA.value('.[1]','VARCHAR(50)')
FROM @testXml.nodes('/invoices') Tab(Col)
OUTER APPLY Tab.Col.nodes('InvoiceNo') AS INVOICE(DATA)
Results
-----------
13537010*1
13537010*3
13537010*4
13537010*5
13537010*7
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply