Using SQL 2008, I have a table (tmpXML) with 2 columns

CREATE TABLE [dbo].[tmpXML](
[pk] [int] IDENTITY(1,1) NOT NULL,
[Field1] [xml] NULL,
CONSTRAINT [PK_tmpXML] PRIMARY KEY CLUSTERED
(
[pk] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I've populated Field1 with XML pulled from a webservice that
represents Orders placed through a website (Customer Info, shipping
address , products, quantities, etc) and now I'm trying to shred the
XML to pull out the required fields to go into the respective tables
in my DB.

I've found a couple of methods for shredding the data into a
column/relational format, but nothing returns data. I get NULL values
for everything I try!!



Here are the T-SQL statements I have tried.. Pretty much the same
result for all of them:
SELECT pk,
Field1.value('(/ArryOfSOrder/SOrder/@Id)[1]', 'int') AS OrdId
FROM tmpXML

--**********************
SELECT --pref.value('Id/int())[1]','int') as OrdId,
pref.value('(Email/text())[1]', 'varchar(50)') as Email,
pref.value('(ConfirmationCode/text())[1]', 'varchar(40)') as
Confirm
,pref.query('Address1') as OrderAddress
FROM
tmpXML CROSS APPLY
Field1.nodes('/ArrayOfSOrder/SOrder') AS SOrder(pref)

This one at least returns the correct number of records (still null
though), but gives me an error if I include the first line.


Is it a problem with the XML formatting in tmpXML.Field1 or in my
query?


Below is a small sample of the data that is stored in the
tmpXML.Field1

<ArrayOfSOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOrder>
<Id xmlns="http://tempuri.org/">19809</Id>
<CreationDate
xmlns="http://tempuri.org/">2009-10-13T15:23:21</CreationDate>
<Email xmlns="http://tempuri.org/">123rgdfg (AT) us (DOT) ibm.com</Email>
<SubTotal xmlns="http://tempuri.org/">51.00</SubTotal>
<Tax xmlns="http://tempuri.org/">Sales Tax (Georgia
only)_3.57^</Tax>
<TxnResponse xmlns="http://tempuri.org/">44047</TxnResponse>
<AuthorizationCode xmlns="http://tempuri.org/" />
<ConfirmationCode xmlns="http://tempuri.org/">1</ConfirmationCode>
<PaymentType xmlns="http://tempuri.org/">Credit Card</PaymentType>
<ShippingType xmlns="http://tempuri.org/">UPS 3 Day
Select</ShippingType>
<ShippingCost xmlns="http://tempuri.org/">0</ShippingCost>
<CustomerDiscount
xmlns="http://tempuri.org/">0.00</CustomerDiscount>
<Processed xmlns="http://tempuri.org/">false</Processed>
<Deleted xmlns="http://tempuri.org/">false</Deleted>
<ExpiryDate xmlns="http://tempuri.org/" xsi:nil="true" />
<Address1 xmlns="http://tempuri.org/">
<Id>200066592</Id>
<FirstName>Willie</FirstName>
<LastName>rtyuytyuj</LastName>
<PhoneNumber>321-999-0320</PhoneNumber>
<FaxNumber />
<Company />
<Street1>45 rtytrytry Lane</Street1>
<Street2 />
<City>Powder Springs</City>
<StateProvince>Georgia</StateProvince>
<ZipPostalCode>55527</ZipPostalCode>
</Address1>
<Address2 xmlns="http://tempuri.org/">
<Id>200066592</Id>
<FirstName>erter</FirstName>
<LastName>ertert</LastName>
<PhoneNumber>777-666-0320</PhoneNumber>
<FaxNumber />
<Company />
<Street1>45 rwerwerer Lane</Street1>
<Street2 />
<City>Powder Springs</City>
<StateProvince>Georgia</StateProvince>
<ZipPostalCode>55555</ZipPostalCode>
</Address2>
<CustomerAccount xmlns="http://tempuri.org/">
<Id>1684</Id>
<Email>wtyrtyard.rtyrty (AT) us (DOT) ibm.com</Email>
<Password>rtytyr</Password>
<CreationDate>2005-05-20T07:03:54</CreationDate>
<LastEditDate>2009-10-13T15:22:11</LastEditDate>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<AccountEnabled>false</AccountEnabled>
<AccountUID />
<Address1>
<Id>9006</Id>
<FirstName>rtyrty</FirstName>
<LastName>rtyrtr</LastName>
<PhoneNumber>678-666-7777</PhoneNumber>
<FaxNumber />
<Company />
<Street1>45 tyrtyrty Lane</Street1>
<Street2 />
<City>Powder Springs</City>
<StateProvince>Georgia</StateProvince>
<ZipPostalCode>55543</ZipPostalCode>
</Address1>
<Address2>
<Id>0</Id>
</Address2>
<Address>
<SAddress>
<Id>9006</Id>
<FirstName>rterte</FirstName>
<LastName>35434534</LastName>
<PhoneNumber>444-777-0320</PhoneNumber>
<FaxNumber />
<Company />
<Street1>45 ertertert Lane</Street1>
<Street2 />
<City>Powder Springs</City>
<StateProvince>Georgia</StateProvince>
<ZipPostalCode>44455</ZipPostalCode>
</SAddress>
</Address>
</CustomerAccount>
<OrderProductVariations xmlns="http://tempuri.org/">
<SOrderProductVariation>
<Id>456456</Id>
<Quantity>3</Quantity>
<Price>27.00</Price>
<Discount>0.00</Discount>
<Deleted>false</Deleted>
<Note01 />
<Note02 />
<Note03 />
<Note04 />
<Note05 />
<Shipped>false</Shipped>
<ProductVariation>
<Id>9</Id>
<ProductCode>40008</ProductCode>
<Name>Shampoo 8 oz.</Name>
<Price>9.00</Price>
<ShippingWeight>0.84</ShippingWeight>
<ShippingPackageType />
<TaxCategoryUID>7</TaxCategoryUID>
<StockCount>9885205</StockCount>
<ReservedQty>5</ReservedQty>
<BackorderedQty>0</BackorderedQty>
<ReOrderMinQty>5</ReOrderMinQty>
<BackOrderMaxQty>0</BackOrderMaxQty>
<BackOrderAllowed>false</BackOrderAllowed>
<OutOfStockVisible>true</OutOfStockVisible>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<Ordering>0</Ordering>
<SProduct>
<Name>Moisture Shampoo</Name>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
</SProduct>
</ProductVariation>
<OrderProductVariationShipments />
</SOrderProductVariation>
<SOrderProductVariation>
<Id>55591</Id>
<Quantity>3</Quantity>
<Price>24.00</Price>
<Discount>0.00</Discount>
<Deleted>false</Deleted>
<Note01 />
<Note02 />
<Note03 />
<Note04 />
<Note05 />
<Shipped>false</Shipped>
<ProductVariation>
<Id>21</Id>
<ProductCode>10001</ProductCode>
<Name>Finishing Lotion 8 oz.</Name>
<Price>8.00</Price>
<ShippingWeight>0.84</ShippingWeight>
<ShippingPackageType />
<TaxCategoryUID>7</TaxCategoryUID>
<StockCount>9998797</StockCount>
<ReservedQty>5</ReservedQty>
<BackorderedQty>0</BackorderedQty>
<ReOrderMinQty>5</ReOrderMinQty>
<BackOrderMaxQty>0</BackOrderMaxQty>
<BackOrderAllowed>false</BackOrderAllowed>
<OutOfStockVisible>true</OutOfStockVisible>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<Ordering>0</Ordering>
<SProduct>
<Name> Finishing Lotion</Name>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
</SProduct>
</ProductVariation>
<OrderProductVariationShipments />
</SOrderProductVariation>
</OrderProductVariations>
<Shipments xmlns="http://tempuri.org/" />
</SOrder>
<SOrder>
<Id xmlns="http://tempuri.org/">19818</Id>
<CreationDate
xmlns="http://tempuri.org/">2009-10-13T16:37:00</CreationDate>
<Email xmlns="http://tempuri.org/">txxxear (AT) yaxxx (DOT) com</Email>
<SubTotal xmlns="http://tempuri.org/">7.50</SubTotal>
<Tax xmlns="http://tempuri.org/" />
<TxnResponse xmlns="http://tempuri.org/" />
<AuthorizationCode xmlns="http://tempuri.org/" />
<ConfirmationCode xmlns="http://tempuri.org/" />
<PaymentType xmlns="http://tempuri.org/">Credit Card</PaymentType>
<ShippingType xmlns="http://tempuri.org/">UPS 2nd Day
Air</ShippingType>
<ShippingCost xmlns="http://tempuri.org/">0</ShippingCost>
<CustomerDiscount
xmlns="http://tempuri.org/">0.00</CustomerDiscount>
<Processed xmlns="http://tempuri.org/">false</Processed>
<Deleted xmlns="http://tempuri.org/">false</Deleted>
<ExpiryDate xmlns="http://tempuri.org/" xsi:nil="true" />
<Address1 xmlns="http://tempuri.org/">
<Id>24444441</Id>
<FirstName>Susuie</FirstName>
<LastName>Rebt</LastName>
<PhoneNumber>334-111-1120</PhoneNumber>
<FaxNumber />
<Company />
<Street1>620 Merge Street</Street1>
<Street2 />
<City>VRVRVRVR</City>
<StateProvince>Alabama</StateProvince>
<ZipPostalCode>33333</ZipPostalCode>
</Address1>
<Address2 xmlns="http://tempuri.org/">
<Id>444446612</Id>
<FirstName>324234</FirstName>
<LastName>Roge233332</LastName>
<PhoneNumber>334-999-2222</PhoneNumber>
<FaxNumber />
<Company />
<Street1>620 merge Street</Street1>
<Street2 />
<City>VRVRVRVR</City>
<StateProvince>Alabama</StateProvince>
<ZipPostalCode>99926</ZipPostalCode>
</Address2>
<CustomerAccount xmlns="http://tempuri.org/">
<Id>200010346</Id>
<Email>xxxxxxxx (AT) yahoo (DOT) com</Email>
<Password>xxxxxx</Password>
<CreationDate>2009-10-13T12:52:09</CreationDate>
<LastEditDate>2009-10-13T16:35:46</LastEditDate>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<AccountEnabled>false</AccountEnabled>
<AccountUID />
<Address1>
<Id>111111111</Id>
<FirstName>werwer N</FirstName>
<LastName>Rogwerwer</LastName>
<PhoneNumber>777-777-3220</PhoneNumber>
<FaxNumber />
<Company />
<Street1>620 nerge Street</Street1>
<Street2 />
<City>ererert</City>
<StateProvince>Alabama</StateProvince>
<ZipPostalCode>333333</ZipPostalCode>
</Address1>
<Address2>
<Id>0</Id>
</Address2>
<Address>
<SAddress>
<Id>333333577</Id>
<FirstName>werwerwe N</FirstName>
<LastName>Rowerwerwe</LastName>
<PhoneNumber>777-7777-3220</PhoneNumber>
<FaxNumber />
<Company />
<Street1>620 nerrtty Street</Street1>
<Street2 />
<City>rtyrtrty</City>
<StateProvince>Alabama</StateProvince>
<ZipPostalCode>55555</ZipPostalCode>
</SAddress>
</Address>
</CustomerAccount>
<OrderProductVariations xmlns="http://tempuri.org/">
<SOrderProductVariation>
<Id>4354562</Id>
<Quantity>1</Quantity>
<Price>7.50</Price>
<Discount>0.00</Discount>
<Deleted>false</Deleted>
<Note01 />
<Note02 />
<Note03 />
<Note04 />
<Note05 />
<Shipped>false</Shipped>
<ProductVariation>
<Id>7</Id>
<ProductCode>456456</ProductCode>
<Name>stuuffffff</Name>
<Price>7.50</Price>
<ShippingWeight>0.84</ShippingWeight>
<ShippingPackageType />
<TaxCategoryUID>7</TaxCategoryUID>
<StockCount>776</StockCount>
<ReservedQty>5</ReservedQty>
<BackorderedQty>0</BackorderedQty>
<ReOrderMinQty>5</ReOrderMinQty>
<BackOrderMaxQty>0</BackOrderMaxQty>
<BackOrderAllowed>false</BackOrderAllowed>
<OutOfStockVisible>true</OutOfStockVisible>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<Ordering>0</Ordering>
<SProduct>
<Name>stuuffffff</Name>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
</SProduct>
</ProductVariation>
<OrderProductVariationShipments />
</SOrderProductVariation>
</OrderProductVariations>
<Shipments xmlns="http://tempuri.org/" />
</SOrder>
<SOrder>
<Id xmlns="http://tempuri.org/">555501</Id>
<CreationDate
xmlns="http://tempuri.org/">2009-10-13T11:31:17</CreationDate>
<Email xmlns="http://tempuri.org/">eertertertert (AT) yahoo (DOT) com</Email>
<SubTotal xmlns="http://tempuri.org/">57.80</SubTotal>
<Tax xmlns="http://tempuri.org/" />
<TxnResponse xmlns="http://tempuri.org/" />
<AuthorizationCode xmlns="http://tempuri.org/" />
<ConfirmationCode xmlns="http://tempuri.org/" />
<PaymentType xmlns="http://tempuri.org/">Credit Card</PaymentType>
<ShippingType xmlns="http://tempuri.org/">UPS
Ground</ShippingType>
<ShippingCost xmlns="http://tempuri.org/">0</ShippingCost>
<CustomerDiscount
xmlns="http://tempuri.org/">0.00</CustomerDiscount>
<Processed xmlns="http://tempuri.org/">false</Processed>
<Deleted xmlns="http://tempuri.org/">false</Deleted>
<ExpiryDate xmlns="http://tempuri.org/" xsi:nil="true" />
<Address1 xmlns="http://tempuri.org/">
<Id>656567675571</Id>
<FirstName>rtertert</FirstName>
<LastName>ereertree</LastName>
<PhoneNumber>22-222-8880</PhoneNumber>
<FaxNumber />
<Company />
<Street1>252 334343 dr</Street1>
<Street2 />
<City>east wfwerwer</City>
<StateProvince>wer354gert</StateProvince>
<ZipPostalCode>123345</ZipPostalCode>
</Address1>
<Address2 xmlns="http://tempuri.org/">
<Id>777572</Id>
<FirstName>trertret</FirstName>
<LastName>ertre</LastName>
<PhoneNumber>517-6565656-8880</PhoneNumber>
<FaxNumber />
<Company />
<Street1>252 efwerwer</Street1>
<Street2 />
<City>eaerest werwer</City>
<StateProvince>wwewfsf</StateProvince>
<ZipPostalCode>44444</ZipPostalCode>
</Address2>
<CustomerAccount xmlns="http://tempuri.org/">
<Id>0</Id>
<CreationDate xsi:nil="true" />
<LastEditDate xsi:nil="true" />
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<AccountEnabled>false</AccountEnabled>
</CustomerAccount>
<OrderProductVariations xmlns="http://tempuri.org/">
<SOrderProductVariation>
<Id>44469</Id>
<Quantity>1</Quantity>
<Price>13.90</Price>
<Discount>0.00</Discount>
<Deleted>false</Deleted>
<Note01 />
<Note02 />
<Note03 />
<Note04 />
<Note05 />
<Shipped>false</Shipped>
<ProductVariation>
<Id>22</Id>
<ProductCode>24447</ProductCode>
<Name>more stuuff.</Name>
<Price>13.90</Price>
<ShippingWeight>1.5</ShippingWeight>
<ShippingPackageType />
<TaxCategoryUID>7</TaxCategoryUID>
<StockCount>9993700</StockCount>
<ReservedQty>5</ReservedQty>
<BackorderedQty>0</BackorderedQty>
<ReOrderMinQty>5</ReOrderMinQty>
<BackOrderMaxQty>0</BackOrderMaxQty>
<BackOrderAllowed>false</BackOrderAllowed>
<OutOfStockVisible>true</OutOfStockVisible>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<Ordering>0</Ordering>
<SProduct>
<Name>morestuff</Name>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
</SProduct>
</ProductVariation>
<OrderProductVariationShipments />
</SOrderProductVariation>
<SOrderProductVariation>
<Id>444470</Id>
<Quantity>1</Quantity>
<Price>7.90</Price>
<Discount>0.00</Discount>
<Deleted>false</Deleted>
<Note01 />
<Note02 />
<Note03 />
<Note04 />
<Note05 />
<Shipped>false</Shipped>
<ProductVariation>
<Id>10</Id>
<ProductCode>40009</ProductCode>
<Name>HCO ertertert.</Name>
<Price>7.90</Price>
<ShippingWeight>0.84</ShippingWeight>
<ShippingPackageType />
<TaxCategoryUID>7</TaxCategoryUID>
<StockCount>97136</StockCount>
<ReservedQty>5</ReservedQty>
<BackorderedQty>0</BackorderedQty>
<ReOrderMinQty>5</ReOrderMinQty>
<BackOrderMaxQty>0</BackOrderMaxQty>
<BackOrderAllowed>false</BackOrderAllowed>
<OutOfStockVisible>true</OutOfStockVisible>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<Ordering>0</Ordering>
<SProduct>
<Name>Conditioner</Name>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
</SProduct>
</ProductVariation>
<OrderProductVariationShipments />
</SOrderProductVariation>
<SOrderProductVariation>
<Id>5551</Id>
<Quantity>1</Quantity>
<Price>9.00</Price>
<Discount>0.00</Discount>
<Deleted>false</Deleted>
<Note01 />
<Note02 />
<Note03 />
<Note04 />
<Note05 />
<Shipped>false</Shipped>
<ProductVariation>
<Id>11</Id>
<ProductCode>40015</ProductCode>
<Name>Moisturizing Conditioner 6 oz.</Name>
<Price>9.00</Price>
<ShippingWeight>1.38</ShippingWeight>
<ShippingPackageType />
<TaxCategoryUID>7</TaxCategoryUID>
<StockCount>4501</StockCount>
<ReservedQty>5</ReservedQty>
<BackorderedQty>0</BackorderedQty>
<ReOrderMinQty>5</ReOrderMinQty>
<BackOrderMaxQty>0</BackOrderMaxQty>
<BackOrderAllowed>false</BackOrderAllowed>
<OutOfStockVisible>true</OutOfStockVisible>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<Ordering>0</Ordering>
<SProduct>
<Name>Moisturizing Conditioner</Name>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
</SProduct>
</ProductVariation>
<OrderProductVariationShipments />
</SOrderProductVariation>
<SOrderProductVariation>
<Id>88472</Id>
<Quantity>2</Quantity>
<Price>18.00</Price>
<Discount>0.00</Discount>
<Deleted>false</Deleted>
<Note01 />
<Note02 />
<Note03 />
<Note04 />
<Note05 />
<Shipped>false</Shipped>
<ProductVariation>
<Id>16</Id>
<ProductCode>40007</ProductCode>
<Name>Hairdress 4 oz.</Name>
<Price>9.00</Price>
<ShippingWeight>0.54</ShippingWeight>
<ShippingPackageType />
<TaxCategoryUID>7</TaxCategoryUID>
<StockCount>997603</StockCount>
<ReservedQty>5</ReservedQty>
<BackorderedQty>0</BackorderedQty>
<ReOrderMinQty>5</ReOrderMinQty>
<BackOrderMaxQty>0</BackOrderMaxQty>
<BackOrderAllowed>false</BackOrderAllowed>
<OutOfStockVisible>true</OutOfStockVisible>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<Ordering>0</Ordering>
<SProduct>
<Name>Creme Hairdress</Name>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
</SProduct>
</ProductVariation>
<OrderProductVariationShipments />
</SOrderProductVariation>
<SOrderProductVariation>
<Id>6673</Id>
<Quantity>1</Quantity>
<Price>9.00</Price>
<Discount>0.00</Discount>
<Deleted>false</Deleted>
<Note01 />
<Note02 />
<Note03 />
<Note04 />
<Note05 />
<Shipped>false</Shipped>
<ProductVariation>
<Id>9</Id>
<ProductCode>40008</ProductCode>
<Name>Conditioning Shampoo 8 oz.</Name>
<Price>9.00</Price>
<ShippingWeight>0.84</ShippingWeight>
<ShippingPackageType />
<TaxCategoryUID>7</TaxCategoryUID>
<StockCount>77205</StockCount>
<ReservedQty>5</ReservedQty>
<BackorderedQty>0</BackorderedQty>
<ReOrderMinQty>5</ReOrderMinQty>
<BackOrderMaxQty>0</BackOrderMaxQty>
<BackOrderAllowed>false</BackOrderAllowed>
<OutOfStockVisible>true</OutOfStockVisible>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
<Ordering>0</Ordering>
<SProduct>
<Name>Conditioning Shampoo</Name>
<Disabled>false</Disabled>
<Deleted>false</Deleted>
</SProduct>
</ProductVariation>
<OrderProductVariationShipments />
</SOrderProductVariation>
</OrderProductVariations>
<Shipments xmlns="http://tempuri.org/" />
</SOrder>
</ArrayOfSOrder>

try this:

SELECT --pref.value('Id/int())[1]','int') as OrdId,
pref.value('declare default element namespace "http://tempuri.org/";(Email/text())[1]', 'varchar(50)') as Email,
pref.value('declare default element namespace "http://tempuri.org/";(ConfirmationCode/text())[1]', 'varchar(40)')
as Confirm
,pref.query('declare default element namespace "http://tempuri.org/";Address1') as OrderAddress
FROM
tmpXML CROSS APPLY
Field1.nodes('/ArrayOfSOrder/SOrder') AS SOrder(pref)

Or this:

;WITH XMLNAMESPACES( 'http://tempuri.org/' AS t )
SELECT
pref.value('t:Id[1]','int') as OrdId,
pref.value('(t:Email/text())[1]', 'varchar(50)') as Email,
pref.value('(t:ConfirmationCode/text())[1]', 'varchar(40)') as Confirm,
pref.query('t:Address1') as OrderAddress
FROM tmpXML
CROSS APPLY Field1.nodes('/ArrayOfSOrder/SOrder') AS SOrder(pref)




"Ckirby" wrote:

Thanks for the replies! Both methods are returning data now!!

Now on to my next sticking point.. What is the syntax to use when you
want to include shredding out the child nodes? For exmaple
'ArrayOfSOrder/SOrder/Address1' , or
'ArrayOfSOrder/SOrder/CustomerAccount/Address1'?

I tried adding a new CROSS APPLY referencing the child node, but the
system didn't like that...




On Fri, 30 Oct 2009 04:01:01 -0700, Bob
<Bob (AT) discussions (DOT) microsoft.com> wrote:

Something like this?

;WITH XMLNAMESPACES( 'http://tempuri.org/' AS t )
SELECT
pref.value('t:Id[1]','int') as OrdId,
pref.value('(t:Email/text())[1]', 'varchar(50)') as Email,
pref.value('(t:ConfirmationCode/text())[1]', 'varchar(40)') as Confirm,
CustomerAccount.Address1.query('.'),
CustomerAccount.Address1.value('t:FirstName[1]', 'VARCHAR(50)') AS FirstName
FROM tmpXML
CROSS APPLY Field1.nodes('/ArrayOfSOrder/SOrder') AS SOrder(pref)
CROSS APPLY SOrder.pref.nodes('t:Address1') AS CustomerAccount(Address1)


What are your expected results?


"Ckirby" wrote:

Using the layout of :
<ArrayOfSOrder>
<SOrder>
<Address1>
<Address2>
<CustomerAccount >
<OrderProductVariations>
<SOrderProductVariation>
<ProductVariation>
<SProduct>

I'd like to get the data back in a 'table' so that I can insert into
an Order Header and oder Detail tables.

I think my goal is get data back formatted like

<SOrder>.Email, <Address1>.<Firstname>, <Address2>.<FirstName>,
<OrderProductVariations>.<SOrderProductVariation>. <Quantity>,
<OrderProductVariations>.<SOrderProductVariation>. <Price>
,
<OrderProductVariations>.<SOrderProductVariation>. <ProductVariation>.<ProductCode>

There's a couple of other fields I'll add, but once the TSQL is
corrected to pull a field from a node, I should be able to easliy pull
any other fields in that node ( I hope!!).

Each SOrder.OrderProductVariations node can have more than one child
nodes (line items on the order), so I'd like to get one 'record' for
each child node. SO in the data sample, the first Order has 2
OrderProductVariations node, so I'd like to get 2 records for it.

123rgdfg (AT) us (DOT) ibm.com, Willie, erter, 3, 27.00, 40008
123rgdfg (AT) us (DOT) ibm.com, Willie, erter, 3, 24.00, 10001

Here's the TSQL I'm trying:

;WITH XMLNAMESPACES( 'http://tempuri.org/' AS t )
SELECT
pref.value('t:Id[1]','int') as OrdId,
pref.value('(t:Email/text())[1]', 'varchar(50)') as Email,
pref.value('(t:ConfirmationCode/text())[1]', 'varchar(40)') as
Confirm,
CustomerAccount.Address1.value('t:FirstName[1]',
'VARCHAR(50)') AS FirstName,
CustomerAccount.Address1.value('t:Id[1]', 'int') AS AddrId,
OrderProductVariations.SOrderProductVariation.quer y('.') as
SOrderProductVariationXML
,OrderProductVariations.SOrderProductVariation.val ue('t:Quantity[1]',
'Decimal') as ProdQty
FROM tmpXML
CROSS APPLY Field1.nodes('/ArrayOfSOrder/SOrder') AS
SOrder(pref)
CROSS APPLY SOrder.pref.nodes('t:Address1') AS
CustomerAccount(Address1)
CROSS APPLY
SOrder.pref.nodes('t:OrderProductVariations') AS
OrderProductVariations(SOrderProductVariation)

It pulls back the XML data in SOrderProductVariationXML, but I get
NULL for the ProdQty value.

Thanks again for all of the help so far!!!!!!!!!




On Fri, 30 Oct 2009 10:26:01 -0700, Bob
<Bob (AT) discussions (DOT) microsoft.com> wrote:

Try this:

;WITH XMLNAMESPACES( 'http://tempuri.org/' AS t )
SELECT
pref.value('t:Id[1]','int') as OrdId,
pref.value('(t:Email/text())[1]', 'varchar(50)') as Email,
pref.value('(t:ConfirmationCode/text())[1]', 'varchar(40)') as Confirm,
CustomerAccount.Address1.query('.'),
CustomerAccount.Address1.value('t:FirstName[1]', 'VARCHAR(50)') AS FirstName,
SOrderProductVariation.SOrder.value('t:Price[1]', 'DECIMAL(10,2)') AS Price,
SOrderProductVariation.SOrder.value('t:ProductVari ation[1]/t:ProductCode[1]', 'DECIMAL(10,2)') AS ProductCode
FROM tmpXML
CROSS APPLY Field1.nodes('/ArrayOfSOrder/SOrder') AS SOrder(pref)
CROSS APPLY SOrder.pref.nodes('t:Address1') AS CustomerAccount(Address1)
CROSS APPLY
SOrder.pref.nodes('t:OrderProductVariations/t:SOrderProductVariation') AS
SOrderProductVariation(SOrder)


This query returns the two rows as per your example. I don't think I
understand what you're trying to do with the address field. Could you post a
simple example of XML ( meaning just a couple of lines) and how you want it
to look and maybe I can help.

"Ckirby" wrote:

Sorry to be unclear on the Address field.. That was basically a
leftover field from where I was playing around trying to get any data
split out. The address node will get split out into First, Last,
Street, City, StateProvince, etc. I should have taken that line out
of the query to avoid confusion.

This looks like exactly what I need! I won't pretend to understand
the syntax you used for the SOrderProductVariation(SOrder) node, but
it's working!! I tried to replicate your logic to pull data from the
SProduct node, but I can't get that syntax to work... But since
there's nothing in that node that I actually need, I'm not going to
worry about it too much!

Thanks for all the help!!!!!!!!!

On Mon, 2 Nov 2009 15:47:01 -0800, Bob <Bob (AT) discussions (DOT) microsoft.com>
wrote:

Please mark the post as helpful!

"Ckirby" wrote:

How do I do that? I'm using the Agent Newsreader.

On Tue, 3 Nov 2009 03:14:02 -0800, Bob <Bob (AT) discussions (DOT) microsoft.com>
wrote: