1

I am trying to convert the tables:

  • tblCustomAttributeSourceSchema that contains the COLUMN DEFINITIONS and
  • tblLabelAttributes that contains the VALUES for each of the columns

For your convenience I created the tables in this sqlfiddle:

http://www.sqlfiddle.com/#!6/b2fde/1

I would like to convert this to a table containing "LabelID" (of type INT - originally from tblLabelAttributes) and "XML_VALUE" (of type XML) as per the following example. So for labelID=688 it should be:

 <attributes>
  <attribute attribute_id="1" value="2.00" />
  <attribute attribute_id="2" value="3.00" />
  <attribute attribute_id="3" value="60.00"/>
</attributes>

The "attribute_id" should be set to the AttributeID from tblCustomAttributeSourceSchema and the "value" should be set to the value in tblLabelAttributes.

If an attribute value is null in '"tblLabelAttributes"' then the "attribute" record should be missing in the XML for that LabelID.

I am not very familiar with XML functionality in SQL Server. I am looking for how I could go about converting the data to such an XML. Any help would be greatly appreciated.

Improve this question
3
  • You don't have a foreign key from one to the other? How do you expect to relate them? Commented Jul 19, 2017 at 19:45
  • You have to map col1 from the schema table: tblCustomAttributeSourceSchema to col1 from tblLabelAttributes (probably needs to be unpivoted) Commented Jul 19, 2017 at 21:00
  • Yeah buy you have multiple rows for col1 on both. I think you need a key from 'AttributeGUID' or 'AttributeId' to get to 'LabelId' on the second table or something. Commented Jul 19, 2017 at 21:06

2 Answers 2

Reset to default
1

Well you seem to have a few issues if the code is exactly like your fiddle:

  1. The data is pivoted, so you would do better to unpivot it.
  2. You don't have a seeding number for an attribute so you need to create that.
  3. I still am not getting how you reference for use to the first table.

I do xml parsing and creation a lot for my job so here is an example of how I would do it:

; WITH d AS 
  (
  SELECT 
   *
  , ROW_NUMBER() OVER(PARTITION BY LabelId ORDER BY val) AS rwn
  FROM tblLabelAttributes
  UNPIVOT (val FOR col IN (col1, col2, col3)) AS upvt
  )
, distincts AS 
  (
  SELECT DISTINCT LabelId
  FROM d
  )
Select 
  LabelId AS "@LabelId"
, (
  SELECT 
    val AS "@value"
  , rwn AS "@attribute_id"
  FROM d y
  WHERE y.LabelId = x.LabelId
  FOR XML PATH('attribute'), TYPE
  )
From distincts x
FOR XML PATH('attributes'), ROOT('ROOT')

Generally speaking nested selects work well with xml creation as you at times need to show a child node relationship and IMHO they work well for this with doing an inner object to an outer object join in the where clause. You can also tell levels of what you are doing by at times having certain parts commented out for the 'for xml ...'. A good xml structure I usually build from the lowest nodes and then go up the tree. That way if I need to debug something I could comment out the last segment and see a section of xml exists on multiple lines. In this example the grouping would be the 'LabelId' if I comment out the last line.

The slightly adjusted version to fit the spec in the question:

; WITH d AS 
  (
  SELECT 
   *
  , ROW_NUMBER() OVER(PARTITION BY LabelId ORDER BY val) AS rwn
  FROM tblLabelAttributes
  UNPIVOT (val FOR col IN (col1, col2, col3)) AS upvt
  )
, distincts AS 
  (
  SELECT DISTINCT LabelId
  FROM d
  )
Select 
  LabelId AS "@LabelId"
, (
  SELECT 
    val AS "@value"
  , rwn AS "@attribute_id"
  FROM d y
  WHERE y.LabelId = x.LabelId
  FOR XML PATH('attribute'), TYPE, ROOT('attributes')
  ) 
From distincts x
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

WOW! This is so close... How do you turn this into 3 rows (1 row for each row in tblLabelAttributes)?
You're right, this is not well structured but this is something I inherited and I don't have control over it.
Unpivot in the CTE.
I really appreciate your help but this query is way above my pay-grade and I could probably figure out in a few hours what you are doing but can you show me what you mean?
Just take out the section of From the line starting with UNPIVOT and up to the select by itself. That should be self explanatory with the results. Essentially it is saying: "Hey you Value (val in my example) I want to reference your column name(col in my example) for these values (col1, col2, col3)." So essentially you get a column for the value and a column for the designator of however many fields you set up. Read more here: technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
|
1

Use the FOR XML in your query:

SELECT *
FROM tblCustomAttributeSourceSchema FOR XML AUTO

SELECT *
FROM tblLabelAttributes FOR XML AUTO

Alternatively, you can create your own XML in your t-sql code:

SELECT LabelID AS "@LabelID",
       col1 AS "Attributes/col1",
       col2 AS "Attributes/col2"
FROM tblLabelAttributes
FOR XML PATH('LabelID')

give an output like this:

<LabelID LabelID="688">
   <Attributes>
      <col1>2.00</col1>
      <col2>3.00</col2>
   </Attributes>
</LabelID>
Improve this answer

1 Comment

I know how to do this. The requirement was specifically for the XML that I provided in the query. Generating elements is much simpler...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.