How to strip ID numbers from a CSV column in sql and inner join with another table?

Question

I am a system admin that honestly doesn't know anything about sql nor programming. I was asked to get some data from a inventory management webapp that we purchased from a third party.

There is an Inventory table and an Inventory description table I already found out a query to inner join the two, but now there is a third table which is the Purchase order table. There is a column called CSV each field in this column contains an XML file with multiple inventory ids per field. The xml file is in this format below:

`<FORMDATA>
  <ITEM>
    <ITPCODE>HBL5266C</ITPCODE>
    <ITPDESC>Plug - 115V - 15A Male</ITPDESC>
  </ITEM>
</FORMDATA>`

So my question is how can I accomplish an sql query that will accomplish the following:

Select PRTCODE from PRT table, SElect PRTDescription from PRTDESC, and finally select CSV but most importantly stript and only select the IPT CODE out of it from the Purchase Order table and link it with PRTCODE?

If it is not possible to accomplish this last portion will I Have to export those files and run some sort of loop to manipulate the strings in those XML files? (They labeled the column as CSV but it appears to be in XML format)

Answer 1

You might be looking for something like this:

DECLARE @PurchaseOrderTable TABLE(ID INT IDENTITY, CSV XML);
INSERT INTO @PurchaseOrderTable VALUES
(N'<FORMDATA>
  <ITEM>
    <ITPCODE>HBL5266C</ITPCODE>
    <ITPDESC>Plug - 115V - 15A Male</ITPDESC>
  </ITEM>
</FORMDATA>')
,(N'<FORMDATA>
  <ITEM>
    <ITPCODE>One more</ITPCODE>
    <ITPDESC>blah blah</ITPDESC>
  </ITEM>
</FORMDATA>');

WITH TheStrippedXML AS
(
    SELECT pot.ID
          ,pot.CSV.value(N'(/FORMDATA/ITEM/ITPCODE/text())[1]',N'nvarchar(max)') AS ItpCode
          ,pot.CSV.value(N'(/FORMDATA/ITEM/ITPDESC/text())[1]',N'nvarchar(max)') AS ItpDesc
    FROM @PurchaseOrderTable AS pot
)
SELECT * FROM TheStrippedXML;

The result

ID  ItpCode     ItpDesc
1   HBL5266C    Plug - 115V - 15A Male
2   One more    blah blah

What you might need:

Use this WITH right before your existing query and use an more JOIN to bind the data found in the table above (adjust the table's name!). Something like

WITH TheStrippedXML AS
(
    SELECT pot.ID
          ,pot.CSV.value(N'(/FORMDATA/ITEM/ITPCODE/text())[1]',N'nvarchar(max)') AS ItpCode
          ,pot.CSV.value(N'(/FORMDATA/ITEM/ITPDESC/text())[1]',N'nvarchar(max)') AS ItpDesc
    FROM @PurchaseOrderTable AS pot
)
SELECT * 
FROM YourFirstTable AS t1
INNER JOIN YourSecondTable AS t2 ON t1.SomeKey=t2.SomeKey
INNER JOIN TheStrippedXML AS t3 ON t3.SomeKey=t2.SomeKey --adjust to your needs...