I am trying to add an attribute to the out most parent node. attached is my code, my results and my expected results to better understand what I'm trying to do. Any help is much appreciated!
My Code:
SELECT
(
SELECT
(SELECT
'quoteRequest' AS '@service'
FOR XML PATH('request'), TYPE),
(SELECT
(SELECT
'Add' as Action,
'generation' AS 'Date/@type',
'MM/DD/YYYY HH:MM' as Date
FOR XML PATH(''), TYPE, ROOT ('Header')),
(SELECT
(SELECT
'' AS Status,
'CustAcctNum' AS 'Enterprise/@customerAcctNum',
'Name' AS 'Enterprise/@name',
'Enterprise Name' AS 'Enterprise'
FOR XML PATH(''), TYPE),
(SELECT
'quoteNumber' AS 'ReferenceNumber/@type',
'true' AS 'ReferenceNumber/@isPrimary',
'VALUE' AS ReferenceNumber
FOR XML PATH('ReferenceNumbers'),
TYPE),
(SELECT
'Special Instructions' AS 'Comment/@type',
'Comment' AS Comment
FOR XML PATH('Comments'),
TYPE),
(SELECT
'Forklift' AS 'EquipmentCode/@desc',
'1' AS 'EquipmentCode/@qty',
'DV' AS EquipmentCode
FOR XML PATH('EquipmentList'),
TYPE),
(SELECT(
SELECT(
SELECT(
SELECT
'earliest' AS '@type',
GETDATE()
FOR XML PATH('Date'), TYPE),
(SELECT
'latest' AS '@type',
GETDATE()
FOR XML PATH('Date'), TYPE)
FOR XML PATH('Pickup'),TYPE),
(SELECT(
SELECT(
SELECT
'earliest' AS '@type',
GETDATE()
FOR XML PATH('Date'), TYPE),
(SELECT
'latest' AS '@type',
GETDATE()
FOR XML PATH('Date'), TYPE)
FOR XML PATH('Drop'),TYPE))
FOR XML PATH('Dates'), TYPE)),
(SELECT
(SELECT
(SELECT
'false' AS '@isresidential',
'' AS 'Alias',
'Name' AS 'Name',
'AddrLine1' AS 'AddrLine1',
'AddrLine2' AS 'AddrLine2',
'City' AS 'City',
'StateProvince' AS 'StateProvince',
'PostalCode' AS 'PostalCode',
'CountryCode' AS 'CountryCode',
(SELECT
'Name' AS 'Name',
(SELECT
'phone' AS 'ContactMethod/@type',
'1' AS 'ContactMethod/@sequenceNum',
'8675309' AS 'ContactMethod'
FOR XML PATH('ContactMethods'), TYPE)
FOR XML PATH('Contact'), TYPE, ROOT('Contacts')),
(SELECT
'Comments' as 'Comments'
FOR XML PATH (''), TYPE)
FOR XML PATH('Address'), TYPE)
FOR XML PATH(''), TYPE, ROOT('Shipper'))),
(SELECT
(SELECT
(SELECT
'false' AS '@isresidential',
'' AS 'Alias',
'Name' AS 'Name',
'AddrLine1' AS 'AddrLine1',
'AddrLine2' AS 'AddrLine2',
'City' AS 'City',
'StateProvince' AS 'StateProvince',
'PostalCode' AS 'PostalCode',
'CountryCode' AS 'CountryCode',
(SELECT
'Name' AS 'Name',
(SELECT
'phone' AS 'ContactMethod/@type',
'1' AS 'ContactMethod/@sequenceNum',
'8675309' AS 'ContactMethod'
FOR XML PATH('ContactMethods'), TYPE)
FOR XML PATH('Contact'), TYPE, ROOT('Contacts')),
(SELECT
'Comments' as 'Comments'
FOR XML PATH (''), TYPE)
FOR XML PATH('Address'), TYPE)
FOR XML PATH(''), TYPE, ROOT('Consignee'))),
(
SELECT(
SELECT(
(SELECT
'false' AS '@stackable',
'1' AS '@sequence',
(SELECT
'UOM' AS 'Quantity/@units',
'1' AS 'Quantity'
FOR XML PATH(''), TYPE),
(SELECT
'UOM' AS 'Weight/@units',
'1' AS 'Weight'
FOR XML PATH(''), TYPE),
(SELECT
'1' AS '@height',
'1' AS '@units',
'1' AS '@width',
'1' AS '@length'
FOR XML PATH('Dimensions'), TYPE),
(SELECT
'freghtClass' AS '@freightClass',
'1' AS '@sequence',
(SELECT
'UOM' AS 'Weight/@units',
'1' AS 'Weight'
FOR XML PATH(''), TYPE),
(SELECT
'1' AS '@height',
'1' AS '@units',
'1' AS '@width',
'1' AS '@length'
FOR XML PATH('Dimensions'), TYPE),
'1' AS 'Quantity'
FOR XML PATH('Item'), TYPE, ROOT('Items'))
FOR XML PATH('HandlingUnit'), TYPE))
FOR XML PATH(''), TYPE, ROOT('HandlingUnits'))),
(
SELECT(
SELECT 'Method' AS Method
FOR XML PATH(''), TYPE),
(SELECT 'true' AS '@thirdParty',
(SELECT
'false' AS '@isresidential',
'' AS 'Alias',
'Name' AS 'Name',
'AddrLine1' AS 'AddrLine1',
'AddrLine2' AS 'AddrLine2',
'City' AS 'City',
'StateProvince' AS 'StateProvince',
'PostalCode' AS 'PostalCode',
'CountryCode' AS 'CountryCode',
(SELECT
'Name' AS 'Name',
(SELECT
'phone' AS 'ContactMethod/@type',
'1' AS 'ContactMethod/@sequenceNum',
'8675309' AS 'ContactMethod'
FOR XML PATH('ContactMethods'), TYPE)
FOR XML PATH('Contact'), TYPE, ROOT('Contacts'))
FOR XML PATH('Address'), TYPE)
FOR XML PATH('BillTo'), TYPE)
FOR XML PATH(''), TYPE, ROOT('Payment'))
FOR XML PATH(''), TYPE, ROOT('Shipment'))
FOR XML PATH(''), TYPE, ROOT('QuoteRequest'))
FOR XML PATH(''), TYPE, ROOT('request'))
My results. Note the first two lines:
**<request>
<request service="quoteRequest" />**
<QuoteRequest>
<Header>
<Action>Add</Action>
<Date type="generation">MM/DD/YYYY HH:MM</Date>
</Header>
<Shipment>
<Status />
<Enterprise customerAcctNum="CustAcctNum" name="Name">Enterprise Name</Enterprise>
<ReferenceNumbers>
<ReferenceNumber type="quoteNumber" isPrimary="true">VALUE</ReferenceNumber>
</ReferenceNumbers>
<Comments>
<Comment type="Special Instructions">Comment</Comment>
</Comments>
<EquipmentList>
<EquipmentCode desc="Forklift" qty="1">DV</EquipmentCode>
</EquipmentList>
<Dates>
<Pickup>
<Date type="earliest">2017-06-12T08:20:27.107</Date>
<Date type="latest">2017-06-12T08:20:27.107</Date>
</Pickup>
<Drop>
<Date type="earliest">2017-06-12T08:20:27.107</Date>
<Date type="latest">2017-06-12T08:20:27.107</Date>
</Drop>
</Dates>
<Shipper>
<Address isresidential="false">
<Alias />
<Name>Name</Name>
<AddrLine1>AddrLine1</AddrLine1>
<AddrLine2>AddrLine2</AddrLine2>
<City>City</City>
<StateProvince>StateProvince</StateProvince>
<PostalCode>PostalCode</PostalCode>
<CountryCode>CountryCode</CountryCode>
<Contacts>
<Contact>
<Name>Name</Name>
<ContactMethods>
<ContactMethod type="phone" sequenceNum="1">8675309</ContactMethod>
</ContactMethods>
</Contact>
</Contacts>
<Comments>Comments</Comments>
</Address>
</Shipper>
<Consignee>
<Address isresidential="false">
<Alias />
<Name>Name</Name>
<AddrLine1>AddrLine1</AddrLine1>
<AddrLine2>AddrLine2</AddrLine2>
<City>City</City>
<StateProvince>StateProvince</StateProvince>
<PostalCode>PostalCode</PostalCode>
<CountryCode>CountryCode</CountryCode>
<Contacts>
<Contact>
<Name>Name</Name>
<ContactMethods>
<ContactMethod type="phone" sequenceNum="1">8675309</ContactMethod>
</ContactMethods>
</Contact>
</Contacts>
<Comments>Comments</Comments>
</Address>
</Consignee>
<HandlingUnits>
<HandlingUnit stackable="false" sequence="1">
<Quantity units="UOM">1</Quantity>
<Weight units="UOM">1</Weight>
<Dimensions height="1" units="1" width="1" length="1" />
<Items>
<Item freightClass="freghtClass" sequence="1">
<Weight units="UOM">1</Weight>
<Dimensions height="1" units="1" width="1" length="1" />
<Quantity>1</Quantity>
</Item>
</Items>
</HandlingUnit>
</HandlingUnits>
<Payment>
<Method>Method</Method>
<BillTo thirdParty="true">
<Address isresidential="false">
<Alias />
<Name>Name</Name>
<AddrLine1>AddrLine1</AddrLine1>
<AddrLine2>AddrLine2</AddrLine2>
<City>City</City>
<StateProvince>StateProvince</StateProvince>
<PostalCode>PostalCode</PostalCode>
<CountryCode>CountryCode</CountryCode>
<Contacts>
<Contact>
<Name>Name</Name>
<ContactMethods>
<ContactMethod type="phone" sequenceNum="1">8675309</ContactMethod>
</ContactMethods>
</Contact>
</Contacts>
</Address>
</BillTo>
</Payment>
</Shipment>
</QuoteRequest>
</request>
My expected results, note the very first line/node is request. Unlike the results I get above, i need the node and attribute to be on the same line:
**<request service=quoteRequest">**
<QuoteRequest>
<Header>
<Action>Add</Action>
<Date type="generation">MM/DD/YYYY HH:MM</Date>
</Header>
<Shipment>
<Status />
<Enterprise customerAcctNum="CustAcctNum" name="Name">Enterprise Name</Enterprise>
<ReferenceNumbers>
<ReferenceNumber type="quoteNumber" isPrimary="true">VALUE</ReferenceNumber>
</ReferenceNumbers>
<Comments>
<Comment type="Special Instructions">Comment</Comment>
</Comments>
<EquipmentList>
<EquipmentCode desc="Forklift" qty="1">DV</EquipmentCode>
</EquipmentList>
<Dates>
<Pickup>
<Date type="earliest">2017-06-12T08:20:27.107</Date>
<Date type="latest">2017-06-12T08:20:27.107</Date>
</Pickup>
<Drop>
<Date type="earliest">2017-06-12T08:20:27.107</Date>
<Date type="latest">2017-06-12T08:20:27.107</Date>
</Drop>
</Dates>
<Shipper>
<Address isresidential="false">
<Alias />
<Name>Name</Name>
<AddrLine1>AddrLine1</AddrLine1>
<AddrLine2>AddrLine2</AddrLine2>
<City>City</City>
<StateProvince>StateProvince</StateProvince>
<PostalCode>PostalCode</PostalCode>
<CountryCode>CountryCode</CountryCode>
<Contacts>
<Contact>
<Name>Name</Name>
<ContactMethods>
<ContactMethod type="phone" sequenceNum="1">8675309</ContactMethod>
</ContactMethods>
</Contact>
</Contacts>
<Comments>Comments</Comments>
</Address>
</Shipper>
<Consignee>
<Address isresidential="false">
<Alias />
<Name>Name</Name>
<AddrLine1>AddrLine1</AddrLine1>
<AddrLine2>AddrLine2</AddrLine2>
<City>City</City>
<StateProvince>StateProvince</StateProvince>
<PostalCode>PostalCode</PostalCode>
<CountryCode>CountryCode</CountryCode>
<Contacts>
<Contact>
<Name>Name</Name>
<ContactMethods>
<ContactMethod type="phone" sequenceNum="1">8675309</ContactMethod>
</ContactMethods>
</Contact>
</Contacts>
<Comments>Comments</Comments>
</Address>
</Consignee>
<HandlingUnits>
<HandlingUnit stackable="false" sequence="1">
<Quantity units="UOM">1</Quantity>
<Weight units="UOM">1</Weight>
<Dimensions height="1" units="1" width="1" length="1" />
<Items>
<Item freightClass="freghtClass" sequence="1">
<Weight units="UOM">1</Weight>
<Dimensions height="1" units="1" width="1" length="1" />
<Quantity>1</Quantity>
</Item>
</Items>
</HandlingUnit>
</HandlingUnits>
<Payment>
<Method>Method</Method>
<BillTo thirdParty="true">
<Address isresidential="false">
<Alias />
<Name>Name</Name>
<AddrLine1>AddrLine1</AddrLine1>
<AddrLine2>AddrLine2</AddrLine2>
<City>City</City>
<StateProvince>StateProvince</StateProvince>
<PostalCode>PostalCode</PostalCode>
<CountryCode>CountryCode</CountryCode>
<Contacts>
<Contact>
<Name>Name</Name>
<ContactMethods>
<ContactMethod type="phone" sequenceNum="1">8675309</ContactMethod>
</ContactMethods>
</Contact>
</Contacts>
</Address>
</BillTo>
</Payment>
</Shipment>
</QuoteRequest>
</request>
I've tried many different things and still cant get it to work correctly. Anybody have any ideas? Thanks much!