XML as input Parameter in SP

Question

XML as input Parameter in SP

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 1

If I could make a couple suggestions for your posts in the future:

1. Try to keep the sample data small. You posted 725 lines of unreadable XML which was hiding a problem that many users on this forum have already solved.

2. All caps when asking for assistance is probably unnecessary. I think you will find plenty of help here by asking in a "regular" written voice.

That said, here is a condensed sample of your data:

<Root xmlns="http://Supervalu.JonesNewInvoiceFlatFileSchema">

<Header xmlns="">

<Body>201212403113INVOICE 061SV01986</Body>

</Header>

<Detail xmlns="">

<Body>0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

<Trailing xmlns="">

<Body>000241</Body>

</Trailing>

</Root>

I think the problem is your T-SQL code is not referencing the namespace that is associated with the <Root> node.

I was able to put together a solution based on the example here:

http://msdn.microsoft.com/en-us/library/ms188282.aspx

DECLARE @XMLdata XML

SET @XMLdata = '

<Root xmlns="http://Supervalu.JonesNewInvoiceFlatFileSchema">

<Header xmlns="">

<Body>201212403113INVOICE 061SV01986</Body>

</Header>

<Detail xmlns="">

<Body>0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

<Trailing xmlns="">

<Body>000241</Body>

</Trailing>

</Root>'

/**************************************************************

I've commented out your INSERT statements and the setting of

@@IDENTITY in order to have a simple example

**************************************************************/

-- insert into tblJonesInvoiceHeaderXML (Header,Trailor)

SELECT t.Item.query('./Header/Body').value('.','NVARCHAR(MAX)') Header,

t.Item.query('./Trailing/Body').value('.','NVARCHAR(MAX)') Trailor

from @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root') AS t(Item)

--

-- See above: declaring the namespace as ns and then associating ns with Root

--

DECLARE @XMLHeaderID INT

-- SELECT @XMlHeaderID = @@IDENTITY

-- insert into tblJonesInvoiceDetailXML (XMlHeaderID,Detail)

SELECT @XMlHeaderID,

t.Item.query('./Detail /Body').value('.','NVARCHAR(MAX)') Detail

from @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root') AS t(Item)

/**************************************************************

Results:

Header Trailor

---------------------------------------------- --------

201212403113INVOICE 061SV01986 000241

(1 row(s) affected)

Detail

----------- ----------------------------------------------------------

NULL 0610605042912201212319500000000000001000J001221650}000000...

(1 row(s) affected)

**************************************************************/

vsoneta SSC Rookie Points: 32 More actions · Answer 2

Thank you so much Jessy for your help.

But in detail table, i just get only 1 row(everything is in one record only)instead of many rows, if the input has many, how can i achieve this.

<Body>201212403113INVOICE 061SV01986</Body>

</Header>

<Body>0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

<Body>0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

</Trailing>

</Root>'

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 3

There is quite a bit of explanation involved so I included it all in the comments within the code. Hope this helps:

DECLARE @XMLdata XML

SET @XMLdata = '

<Root xmlns="http://Supervalu.JonesNewInvoiceFlatFileSchema">

<Header xmlns="">

<Body>201212403113INVOICE 061SV01986</Body>

</Header>

<Detail xmlns="">

<Body>AAA0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

<Detail xmlns="">

<Body>BBB0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

<Trailing xmlns="">

<Body>000241</Body>

</Trailing>

</Root>'

/**************************************************************

The .nodes method returns a row of XML for every node in

the xml which matches the XQuery. In this case, the

XQuery is ns:Root, so it returns one row since there is only

one <Root> node in the XML.

***************************************************************/

SELECT t.Item.query('.') AS xml_value

FROM @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root') AS t(Item)

/**************************************************************

Here is an example of .nodes returning two rows from a

different XQuery: ns:Root/Detail

***************************************************************/

SELECT t.Item.query('.') AS xml_value

FROM @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root/Detail') AS t(Item)

/**************************************************************

The .query specifies an XQuery against an instanace of an

xml object. So if I use .nodes to return a match for all

nodes with <Root>, I will get one xml object returned.

Then I can query that xml object for the data in the body

node under the header node

***************************************************************/

SELECT t.Item.query('./Header/Body') AS xml_value

FROM @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root') AS t(Item)

/**************************************************************

Now I have the data I want, but it's in xml, so I need to

convert it to the data type for my table. The .value method

performs an XQuery against the xml and returns it as a SQL

data type.

***************************************************************/

SELECT t.Item.query('./Header/Body').value('.','NVARCHAR(MAX)') AS Header

FROM @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root') AS t(Item)

/**************************************************************

You can use this same process to also get the Trailer value.

But if you have multiple headers or trailers in a single <Root>

node then this is only going to return one value. It appears

that .query only gets the first instance of the match for the

XQuery.

***************************************************************/

SELECT

t.Item.query('./Header/Body').value('.','NVARCHAR(MAX)') AS Header,

t.Item.query('./Trailing/Body').value('.','NVARCHAR(MAX)') AS Trailer

FROM @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root') AS t(Item)

/**************************************************************

This is why the original code was failing to return multiple

detail values. Our XQuery was looking for all nodes that matched

<Root> in the xml but there was only one. So we only got

the first query result for Detail returned from that one node.

We need to do an XQuery for all <Detail> nodes under <Root>

and then query the <Body> node in order to get the data.

***************************************************************/

SELECT

t.Item.query('Body').value('.','NVARCHAR(MAX)') Detail

FROM @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root/Detail') AS t(Item)

vsoneta SSC Rookie Points: 32 More actions · Answer 4

Once again thank you so much Jesse for your detailed explanation, now I understood as to how the query works for different scenarios.

Thank you so much.

vsoneta SSC Rookie Points: 32 More actions · Answer 5

How about the query from table

ie I have the whole file in column of a table then how would the query look like

I have a table --tblXMLData which has XMLData xml datatype

which has the data --

<Body>201215203105INVOICE 061SV02014</Body>

</Header>

<Body>0610605052712201215119500000000000001000J001763080}00000000000000000000017630Q0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000320121510000000000010527120000000N0000000{0000{0000{0000{00000000{0000000000{0000017630Q000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

</Detail>

</Trailing>

</Root>

then how would the below query get from the table ?--

insert into tblJonesInvoiceHeaderXML (Header,Trailor,createddate)

SELECT t.Item.query('./Header/Body').value('.','NVARCHAR(MAX)') Header

,t.Item.query('./Trailing/Body').value('.','NVARCHAR(MAX)') Trailor

,GETDATE()

from @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema";ns:Root') AS t(Item)

SELECT @XMLHeaderID = @@IDENTITY

insert into tblJonesInvoiceDetailXML (XMLHeaderID,Detail)

SELECT @XMlHeaderID ,

t.Item.query('Body').value('.','NVARCHAR(MAX)') Detail

from @XMLdata.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root/Detail') AS t(Item)

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 6

Here is another sample which will allow you to get the header, trailer, and body data for each row of XML:

/*********************************************************************************

Set up XML data for two rows in a sample table

**********************************************************************************/

DECLARE

@xml1 XML,

@xml2 XML

SET @xml1 = '

<Root xmlns="http://Supervalu.JonesNewInvoiceFlatFileSchema">

<Header xmlns="">

<Body>201212403113INVOICE 061SV01986</Body>

</Header>

<Detail xmlns="">

<Body>AAA0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

<Detail xmlns="">

<Body>BBB0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

<Trailing xmlns="">

<Body>000241</Body>

</Trailing>

</Root>'

SET @xml2 = '

<Root xmlns="http://Supervalu.JonesNewInvoiceFlatFileSchema">

<Header xmlns="">

<Body>201212403114INVOICE 999ABC1234</Body>

</Header>

<Detail xmlns="">

<Body>YYY0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

<Detail xmlns="">

<Body>ZZZ0610605042912201212319500000000000001000J001221650}00000000000000000000012216N0000000{000000000000000000J0000000000020-95 CREDIT COU 0000000000S9299000 0061000000220121230000000000010429120000000N0000000{0000{0000{0000{00000000{0000000000{0000012216N000{000000{00000{00000{00000{00000{00000000000000000000{000000{000000{000000{000000{000000{000000{000000{000000{000000{000000000000000000000000000000</Body>

</Detail>

<Trailing xmlns="">

<Body>000241</Body>

</Trailing>

</Root>'

/*********************************************************************************

Load the data

**********************************************************************************/

DECLARE @invoices TABLE (ID INT NOT NULL, Data XML)

INSERT @invoices

SELECT 1, @xml1 UNION ALL

SELECT 2, @xml2

/*********************************************************************************

Use the exact same technique as described earlier. The only difference is that

the XML extraction needs to be applied against every row instead of just a single

variable. Use the CROSS APPLY command to achieve this.

Since you will also need the associated Header and Trailer for each row of <Body>

data, use the '../' to go up a level and get the <Header> and <Trailing> XML

**********************************************************************************/

SELECT

t.Item.query('../Header/Body').value('.','NVARCHAR(MAX)') AS Header,

t.Item.query('../Trailing/Body').value('.','NVARCHAR(MAX)') AS Trailer,

t.Item.query('Body').value('.','NVARCHAR(MAX)') Detail

FROM @invoices i

CROSS APPLY i.Data.nodes('declare namespace ns="http://Supervalu.JonesNewInvoiceFlatFileSchema"; ns:Root/Detail') AS t(Item)

The one part I haven't included how to load this flattened data into a normalized table structure. That really depends on your own choice.

One example:

1. Load all your XML data into a staging table

2. Get the distinct headers and load them into your order header table

3. Use the OUTPUT command to capture all the identity values and headers that were inserted

4. Put that data in a temp table

5. Match up the headers in the temp table to the headers in the staging table

6. Use that match to determine the identity value for your <Body> data

7. Insert that into the order detail table.

But that's just my initial thought. You may come up with a more efficient way to approach it.