SQL 2005 XQuery  Node with multiple attribute help

Im new to XML and have to generate the follow mapping node from a SQL 2005 stored procedure for products to be imported into another application:

<Product>
  <Title>Hell Girl - Lithograph</ Title>
  <SKU>112233445566</SKU>
  <ManufacturerPartNumber>1234567</ManufacturerPartNumber>
  <ProductType>Merchandise</ProductType>
  <AvaiableStartDate>2008-10-07</AvaiableStartDate>
  <AvaiableStopDate>1900-01-01</AvaiableStopDate>

  <Mappings>
    <Entity EntityType="Distributor" Name="ABC Company"/>
    <Entity EntityType="Manufacturer" Name="EFG Manufacturer"/>
    <Entity EntityType="Category" Name="Lithograph"/>
    <Entity EntityType="Property" Name="Hell Girl"/>
  </Mappings>

</Product>

But my code generates this:

<Product>
  < Title >Hell Girl - Lithograph</ Title>
  <SKU>112233445566</SKU>
  <ManufacturerPartNumber>1234567</ManufacturerPartNumber>
  <ProductType>Merchandise</ProductType>
  <AvaiableStartDate>2008-10-07</AvaiableStartDate>
  <AvaiableStopDate>1900-01-01</AvaiableStopDate>

  <Mappings>
    <Entity EntityType="Distributor">ABC Company</Entity>
    <Entity EntityType="Manufacturer">EFG Manufacturer</Entity>
    <Entity EntityType="Category">Lithograph</Entity>
    <Entity EntityType="Property">Hell Girl</Entity>
  </Mappings>

</Product>

Attached is code snippet from my stored procedure. Is there a way to generate multiple attributes for a node or is this not supported in SQL 2005 XML?

Thank you,
Larry B.
SELECT
	fun.Title AS "Title",
	sap.UPCNumber AS "SKU",	
	sap.MaterialNumber AS "ManufacturerPartNumber",
	fun.ProductType AS "ProductType",
	fun.ReleaseDate AS "AvaiableStartDate",
	sap.StopSalesDate AS "AvaiableStopDate",
	(
	SELECT
	'ABC Company' AS [Distributor],
	fun2.Manufacturer,
	fun2.Category,
	fun2.Property
	FROM dbo.AspDNSFStaging_FUNCat_Material fun2
	INNER JOIN dbo.AspDNSFIStaging_SAP_FUN_Material sap2 ON CAST(fun2.UPC AS BIGINT) = CAST(sap2.UPCNumber AS BIGINT)
	WHERE sap2.UPCNumber = sap.UPCNumber		
	FOR XML RAW ('Entity'), TYPE
	)
	.query
	('
	<Mappings>  
    <Entity EntityType="Distributor">{data(/Entity/@Distributor)}</Entity>
    <Entity EntityType="Manufacturer">{data(/Entity/@Manufacturer)}</Entity> 
    <Entity EntityType="Category">{data(/Entity/@Category)}</Entity> 
    <Entity EntityType="Property">{data(/Entity/@Property)}</Entity>
    </Mappings>
	')
	FROM dbo.AspDNSFStaging_FUNCat_Material fun
	INNER JOIN dbo.AspDNSFIStaging_SAP_FUN_Material sap ON CAST(fun.UPC AS BIGINT) = CAST(sap.UPCNumber AS BIGINT)
ORDER BY sap.UPCNumber
FOR XML PATH('Product'), TYPE

Open in new window

larrybyeAsked:
Who is Participating?
 
tigin44Commented:
this should give you the results
SELECT
        fun.Title AS "Title",
        sap.UPCNumber AS "SKU", 
        sap.MaterialNumber AS "ManufacturerPartNumber",
        fun.ProductType AS "ProductType",
        fun.ReleaseDate AS "AvaiableStartDate",
        sap.StopSalesDate AS "AvaiableStopDate",
        (
        SELECT
        'ABC Company' AS [Distributor],
        fun2.Manufacturer,
        fun2.Category,
        fun2.Property
        FROM dbo.AspDNSFStaging_FUNCat_Material fun2
        INNER JOIN dbo.AspDNSFIStaging_SAP_FUN_Material sap2 ON CAST(fun2.UPC AS BIGINT) = CAST(sap2.UPCNumber AS BIGINT)
        WHERE sap2.UPCNumber = sap.UPCNumber            
        FOR XML RAW ('Entity'), TYPE
        )
        .query
        ('
        <Mappings>  
    <Entity EntityType="Distributor" Name="{data(/Entity/@Distributor)}"/>
    <Entity EntityType="Manufacturer" Name="{data(/Entity/@Manufacturer)}"/> 
    <Entity EntityType="Category" Name="{data(/Entity/@Category)}"/> 
    <Entity EntityType="Property" Name="{data(/Entity/@Property)}"/>
    </Mappings>
        ')
        FROM dbo.AspDNSFStaging_FUNCat_Material fun
        INNER JOIN dbo.AspDNSFIStaging_SAP_FUN_Material sap ON CAST(fun.UPC AS BIGINT) = CAST(sap.UPCNumber AS BIGINT)
ORDER BY sap.UPCNumber
FOR XML PATH('Product'), TYPE

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.