SQL query to XML output

Transact-SQL
1

Hello sql expert,

I am trying to query these two fields from Product table to XML output but I having an issue, what I am trying to produce is an xml like below:

<Products_Inventory>
	<Inventory>
		<Product Color="Black">1025</Product>
		<Product Color="White">1123</Product>
		<Model/>
	</Inventory>
</Products_Inventory>

From my sql table:

SELECT	Product_A 
	,Product_B 
	,Model
FROM Product

The value of table Product is:

01

How do I update my above query to get the XML output I am trying to achieve above?

Thank you all

2

please provide more sample data instead of a screen shot .

do

create table #products(Product_A int, Product_B int, Model int )

insert into #products
select 1025, 1123, ''
3

Hi yosiasz,

The table that I am using currently very basic, just return 3 fields of Product A (1025), Product B (1123) and Model.

I am currently queering against that table to get the XML out. So, here is my basic query:

SELECT	Product_A 
	,Product_B 
	,Model
FROM Product

FOR XML PATH ('Products_Inventory')

and the XML output I am trying to achieve is:

<Products_Inventory>
	<Inventory>
	     <Product Color="Black">1025</Product>
	     <Product Color="White">1123</Product>
	     <Model/>
	</Inventory>
</Products_Inventory>

Thanks yosiasz

4

is this a homework or real life scenario?
What determines the colors?

5

hi yosiasz, this is a life scenario and i am using small scale of product inventory for better understanding. The colors represent the product's color.

thanks yosiasz

6

Why does one product have 2 Product columns? what is the meaning of that?

7

hi yosiasz,

The 2 products just a small scale that I am using for better understanding querying sql query to xml. The format I am working on is to achieve like this example. So, instead using large product records set, I created just very basic for an example.

8

then I would encourage you to read the documentation. your table design and the resulting xml you want dont jive.

https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-path-mode
then keep playing by changing things.

create table #products(Product_A int, Product_B int, Model int )

insert into #products
select 1025, 1123, null

SELECT Product_A , 
	  Product_B,
	  Model
FROM #products
--FOR XML PATH ('Products_Inventory')
--FOR XML AUTO, ELEMENTS XSINIL;
FOR XML PATH ('Products_Inventory'), ELEMENTS XSINIL;
--FOR XML PATH ('Products_Inventory'), ELEMENTS ;

drop table #products

<Products_Inventory xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Product_A>1025</Product_A>
  <Product_B>1123</Product_B>
  <Model xsi:nil="true" />
</Products_Inventory>
1 Like
9

Thanks yosiasz, for the link, I would definitely read this process and also thanks for the query.