Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

XML shred of XML data in table returing null values

18 views
Skip to first unread message

Ckirby

unread,
Oct 29, 2009, 11:56:19 PM10/29/09
to
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/">123r...@us.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>wtyrtyar...@us.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/">txx...@yaxxx.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>xxxx...@yahoo.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/">eerter...@yahoo.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>

daw

unread,
Oct 30, 2009, 3:29:13 AM10/30/09
to
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

Bob

unread,
Oct 30, 2009, 7:01:01 AM10/30/09
to
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

unread,
Oct 30, 2009, 12:05:45 PM10/30/09
to
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...

Bob

unread,
Oct 30, 2009, 1:26:01 PM10/30/09
to
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

unread,
Nov 2, 2009, 1:10:07 PM11/2/09
to
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.

123r...@us.ibm.com, Willie, erter, 3, 27.00, 40008
123r...@us.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.query('.') as
SOrderProductVariationXML
,OrderProductVariations.SOrderProductVariation.value('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

unread,
Nov 2, 2009, 6:47:01 PM11/2/09
to
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:ProductVariation[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

unread,
Nov 3, 2009, 12:02:43 AM11/3/09
to
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 <B...@discussions.microsoft.com>
wrote:

Bob

unread,
Nov 3, 2009, 6:14:02 AM11/3/09
to
Please mark the post as helpful!

Ckirby

unread,
Nov 3, 2009, 12:00:56 PM11/3/09
to
How do I do that? I'm using the Agent Newsreader.

On Tue, 3 Nov 2009 03:14:02 -0800, Bob <B...@discussions.microsoft.com>
wrote:

Bob

unread,
Nov 11, 2009, 6:32:03 AM11/11/09
to
You may have to log on to the webpage and click the 'Yes' button in the 'Was
this post helpful to you?' section:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.sqlserver.xml&cat=en_us_aaf14fdd-6ac4-4c25-beab-9b05c409044a&lang=en&cr=us

0 new messages